By using the XML native store instead of shredding the documents, you can reduce development time; shredding often results in complex mappings requiring significant knowledge of the data when developing SQL to retrieve it.
Storing XML in DB2 for z/OS
XML is stored similarly to the way Large Objects (LOBs) are stored. XML objects contain data not stored in the base table space, but rather in a separate table space. The XML objects are automatically created by DB2 when an XML column is defined to a table. All the utilities either support or tolerate XML, with some specific XML keyword support. Support and maintenance for XML have more considerations than LOBs, such as table space size limit, compression, and indexes.
To estimate basic XML storage after parsing, you can use a 1:1 average as a guideline. It could be as small as 0.3 (stripping whitespace and using compression) of the original document or as large as 1.5 (preserving whitespace without compression) of the original document. You can store XML data and complex data without mapping in DB2 with optional compression (with the potential for up to 70 percent savings). An XML table space always uses a 16KB page size; you must ensure a 16K buffer pool exists for this. For non-range-partitioned base table spaces, a Partition By Growth (PBG) table space is used for XML. XML table space partitioning follows base table partitioning for the range-partitioned table space. The number of rows that can fit into a base table partition is limited by the number of documents that fit into an XML partition. If the XML tree nodes can’t fit on one 16K record, they’re split into multiple records. The LOG, LOCKMAX, and COMPRESS settings are inherited from the base table space. Default values are provided for the other attributes.
XML data is a corporate asset just like relational data. It must be managed, shared, analyzed, and protected like any other critical data. There are additional considerations when working with XML objects, especially when it comes to recovery. To be able to recover a base table space, take image copies of all related objects. Use LISTDEF to define a list of related objects (keyword XML or ALL). For a point-in-time recovery (RECOVER TORBA, TOLOGPOINT), all related objects, including XML objects, must be recovered to a consistent point in time.
Similar to referential integrity, there’s a relationship between the base table space and the XML auxiliary table space. The CHECK DATA utility can be used on the base table space to determine consistency between the base table space and the XML table space. Use the CHECK INDEX utility to find problems with the DOCID and NODEID indexes. REBUILD INDEX can be used to repair the problems. The COPY and RECOVER utilities can be used together on dependent objects (XML table space, DOCID/NODEID indexes). QUIESCE TABLESPACESET can achieve a single point of consistency. REORG and RUNSTATS utilities for XML table spaces are executed independently from the base table spaces, but LISTDEF can be used to execute these utilities against XML table spaces together with base tables.
Relational to XML
Data stored relationally (as columns in rows) can be converted into XML by using a powerful set of built-in DB2 scalar and aggregate functions. Invoking these functions can be simple or complex; you can transform any data in DB2 into an XML document of any complexity.
Using the DB2 sample database, for example, we can take the basic information about our fictitious company, The Spiffy Computer Service Company, and generate XML documents that contain information about the company’s departments and employees. Figure 1 is a simple example that retrieves the names of our employees in a basic XML document.
You can take many combinations of the built-in XML functions to produce complex XML documents for transmission back to your application, or for communications to other information systems via such things as messaging or file transfer.
Figure 2 takes advantage of several XML functions to build a document containing the information for some departments of our company and all the employees in those departments.