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

How comfortable are you with your knowledge of LOB and XML concepts for IBM® DB2® for z/OS®? Do you think that you could participate in a technical conversation or listen to a technical presentation with complete understanding of what is being proposed or stated? Sometimes you need to simply step back and return to the basics. This article provides just that: a basic overview of LOB and XML data types in a z/OS environment.

Share:

Elaine Morelli (morellie@us.ibm.com), Executive IT Specialist, IBM

Elaine Morelli is an executive IT specialist on the North America IBM z/Series Database Tools Team. Her expertise is in DB2 database tools with a specialization in DB2 administration. She has over 25 years of IT experience, with 10 at IBM as a technical sales specialist. In the past, she has been an application developer, a DOS/VSE systems programmer, and a database adminstrator for DB2, IDMS, Informix, and Oracle. She is a certified DB2 database administrator and has presented at various local user groups and technical conferences across the United States. She is the IBM sponsor for the Tampa Bay Relational User Group.



15 July 2010

Also available in Spanish

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

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

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

Resources

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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