Contents


Getting back to basics: LOBs and XML objects for DB2 for z/OS

Comments

Overview

LOB and XML complex data types have extended the capabilities of DB2 for z/OS in order to support the needs of today's applications. The infrastructure for storing LOB and XML data is similar in nature because both data types use the concept of an auxiliary set of objects.

LOB data type

Support for large objects (LOBs) was introduced with DB2 V6. Its usage, like many other new DB2 features, was slowly adopted. In the beginning, the administration of LOBs was difficult to manage because all of the SQL to create the objects had to be manually coded. While this method provides the most flexibility in regards to naming standards, it can also be error-prone and time consuming.

DB2 V8 delivered the ability to use the CURRENT RULES = 'STD' service register to create LOBs. The CURRENT RULES = 'STD' service register enables DB2 to implicitly create all of the auxiliary objects when the BASE table is created. However, DB2 dictates the naming standard. The table space name, for example, will begin with an "L" followed by seven randomly generated characters. The table name consists of the first five characters of the base table name, the first five characters of the LOB column name, and eight randomly generated characters. These naming conventions are shown in the example in the following figure:

Figure 1. DB2 naming conventions for auxiliary objects when CURRENT RULES = 'STD' is used
A screenshot of an ISPF panel that shows the DB2 naming conventions for auxiliary objects when CURRENT RULES = 'STD' is used
A screenshot of an ISPF panel that shows the DB2 naming conventions for auxiliary objects when CURRENT RULES = 'STD' is used

Although the CURRENT RULES = 'STD' service register makes life much easier, many DBAs continue to define LOBs by using the manual method primarily because of their internal naming standards. This flexibility is not available when dealing with XML.

XML data type

Extensible Markup Language (XML) is the by-product of an evolutionary cycle that began with IBM's Generalized Markup Language (GML) in 1969. XML is platform and vendor independent and has evolved into the industry standard for exchanging data across different systems, platforms, and applications. XML is self-describing and easy to extend, which makes it a very flexible data model for all types of data, both structured and unstructured.

Prior to DB2 9, XML documents could be stored in DB2 for z/OS as either a LOB or "shredded" and stored in multiple relational tables. Unfortunately, storing XML documents in this fashion inhibits the ability to take full advantage of the benefits of XML, such as the search capability. Storing XML data in this fashion also makes it very difficult, if not impossible, to re-create a full XML document on demand. DB2 9, however, provides the ability to store XML data in DB2 in a parsed hierarchical format, which enables the exploitation of full XML capabilities within the database engine.

When using DB2 9's pureXML capabilities, DB2 implicitly creates all of the XML auxiliary objects. Some users might think that this is an undesirable restriction because it is highly probable that the naming standard does not meet existing standards. However, remember that the auxiliary objects are not directly accessed through SQL, and the benefits of increased productivity and minimal errors should not be overlooked.

XML uses a DOCID rather than a ROWID. Conceptually, both mechanisms are the same. The auxiliary table space inherits the attributes from the table space storing the BASE table. Only one DOCID is required in the base table regardless of the number of XML columns. The definition of the DOCID is automatically generated by DB2 when an XML column type is added to the base table. The name is DB2_GENERATED_DOCID with a data type of BIGINIT. The auxiliary table has the same name as the base table with an "X" appended to the front of it. The DOCID index on the BASE table appends a I_DOCID prefix to the name of the BASE table. The NODEID index on the auxiliary index name appends a I_NODEIDX to the name of the BASE table. These naming conventions are shown in the following example:

Figure 2. Naming conventions for XML auxiliary objects
A screenshot of an ISPF panel that shows the naming conventions for XML auxiliary objects
A screenshot of an ISPF panel that shows the naming conventions for XML auxiliary objects

Summary

Although LOB objects can still be managed manually, the delivery of the CURRENT RULES = 'STD' special register in DB2 V8 vastly improved productivity. This functionality has been extended even further with the automatic implicit creation of XML object types provided by DB2 9.

The following table summarizes how auxiliary objects for LOB and XML data types can be created and how they are named and stored.

Table 1. Creation, naming conventions, and storage of auxiliary objects
LOB Data TypeXML Data Type
Auxiliary objects can be created manuallyAuxiliary objects cannot be created manually
Auxiliary objects can be created implicitly by DB2 9 when CURRENT RULES = 'STD' is usedAuxiliary objects can be created implicitly by DB2 9
Naming conventions for auxiliary objects when CURRENT RULES = 'STD' is used:
  • Auxiliary table space: Begins with an L followed by seven randomly generated characters
  • Auxiliary table: The first five characters of the BASE table name, the first five characters of the LOB column name, and eight randomly generated characters
  • ROWID: DB2_GENERATED_ROWID
Naming conventions for auxiliary objects:
  • Auxiliary table space: Begins with an X followed by seven randomly generated characters
  • Auxiliary table: Begins with an X followed by the name of the BASE table
  • NODEID index (defined on the auxiliary table): Begins with I_NODEIDX followed by the BASE table name
  • DOCID index (defined on the BASE table): Begins with I_DOCID followed by the BASE table name
  • DOCID: DB2_GENERATED_DOCID
Auxiliary tables are stored as type X in SYSIBM.SYSTABLESAuxiliary tables are stored as type P in SYSIBM.SYSTABLES

Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=500474
ArticleTitle=Getting back to basics: LOBs and XML objects for DB2 for z/OS
publish-date=07152010