Detailed description of the custom XML storage and use environment in DB2

BKJIA: If you use IBM DB2 for z/OS, DB2 for Linux, UNIX, and Windows (LUW), there is no problem. Let's review when to use XML storage, and some best practices for customizing XML storage!

For an image description, I will use an XML document with the following content:

 
 

  
  
<order OrderID="9001" OrderDate="2009-10-18">> 
  
  
 <customerID>26914</customerID> 
  
  
 <item id="LK-486"> 
  
  
  <name>Magic Potion</name> 
  
  
  <size>300ml</size> 
  
  
  <price>19.99</price> 
  
  
 </item> 
  
  
 <item id="VF-145"> 
  
  
  <name>Crystal Ball, Deluxe</name> 
  
  
  <color>crystal clear</color> 
  
  
  <price>295.00</price> 
  
  
 </item> 
  
  
</order> 
 
 

It shows an order XML document that includes the order ID, date, customer ID, and other items. Note that some items have different descriptions, such as size and color. We assume that we need to manage many XML documents similar to this type in DB2.

How to split and reorganize XML

In another article, "15 DB2 pureXML performance best practices", I talked about how to choose document granularity wisely, in fact, it is necessary to match the XML document stored in DB2 with the business logic object of the application and the main access granularity.

In our example, assuming that the order changes frequently, reading, adding, or deleting entries in the order is the most critical operation and requires the best performance. In this case, you can consider splitting the order document and storing each entry as an independent document in each row of the DB2 table. This storage method is compared with the method used to store the original complete order document) the advantage is that it makes operations on the stored data easier and faster:

You can use a single row to read and retrieve an entry. You do not need to extract entries from a complete order document;

You can simply delete an entry from the order by deleting the rows in the table, and you do not need to operate the complete order document;

You can quickly Insert a new entry into the order without the need to complete the order document.

This easy addition and removal of order entries is particularly valuable in DB2 9 for z/OS, because this version does not support adding or deleting elements in existing XML documents.
The following code shows the definition of a table and the INSERT statement for splitting an order document. The related columns store the order ID, customer ID, order date, and entry sequential number respectively.

 
 

  
  
CREATE TABLE items(ordID INTEGER, custID INTEGER,  
  
  
                                   odate DATE, seqNo INTEGER, item XML);  
  
  
INSERT INTO items(ordID, custID, odate, seqno, item)  
  
  
 SELECT T.ordID, T.custID, T.odate, T.seqno, XMLDOCUMENT( T.item)  
  
  
 FROM 
  
  
  XMLTABLE('$d/order/item' PASSING cast(? AS XML) "d" 
  
  
   COLUMNS  
  
  
    ordID        INTEGER    PATH      '../@OrderID',  
  
  
    custID       INTEGER    PATH      '../customerID' 
  
  
    odate        DATE       PATH      '../@OrderDate',  
  
  
    seqNo        FOR ORDINALITY,  
  
  
    item         XML        PATH      '.') AS T; 
 
 

The entry information is stored in XML format, because the entry may have different elements and attributes, such:

 
 

  
  
ORDID     CUSTID     ODATE     SEQNO     ITEM  
  
  
-----     -----     ------     -----     -----  
  
  
9001     26914     10/18/2009     1   <item id="LK-486">  
  
  
                                        <name>Magic Potion</name>  
  
  
                                        <size>300ml</size>  
  
  
                                        <price>19.99</price>  
  
  
                                      </item>  
  
  
9001     26914     10/18/2009     2   <item id="VF-145">  
  
  
                                        <name>Crystal Ball, Deluxe</name>  
  
  
                                        <color>crystal clear</color>  
  
  
                                        <price>295.00</price>  
  
  
                                      </item>  
  
  
2 record(s) selected. 
 
 

The INSERT statement includes an XMLTABLE function. This function extracts the values inserted into the items table from the input XML file. It splits the input XML file and generates a document with independent entries. The XMLTABLE function includes a parameter through which the application can pass an order document and use the XPath expression $ d/order/item. The XMLTABLE function generates a row of data for each entry in the input document, then, the Order ID, customer ID, and order date are extracted. The special column definition for ordinality is numbered FOR each row generated. The XMLDOCUMENT function ensures that each entry segment can be inserted as an independent XML document.

The code above shows the data in the items table after the XML document is inserted using the INSERT statement. The code below shows how to recreate the original order document, the XMLELEMENT and XMLATTRIBUTES functions use the values of relevant columns in the items table to construct the top document. The XMLAGG function combines all entries and finally forms a complete order document. Note that xmlparts includes an optional order by clause in the seqno column. This ensures that the ORDER document after restoration is consistent with the ORDER in the original document.

 
 

  
  
SELECT XMLELEMENT(name "order",  
  
  
         XMLATTRIBUTES(ordID AS "OrderID", odate as "OrderDate"),  
  
  
         XMLELEMENT(name "customerID", custID)  
  
  
         XMLAGG(item ORDER BY seqno) )  
  
  
FROM items  
  
  
WHERE ordID = 9001  
  
  
GROUP BY ordID, odate, custID; 
 
 

Use generate Columns

The new IBM DB2 pureXML Feature in DB2 9.7 for LUW allows you to use XML columns with Database partition Feature Database Partitioning Feature, DPF), range Partition Table and multi-dimensional cluster MDC) tables, however, the partition or cluster key must be composed of related columns. You have seen how to use INSERT and XMLTABLE to extract values from XML documents to related columns. You can use these associated columns to partition or cluster tables. If you prefer to use simple INSERT statements in a program and do not know how to extract data, you can consider using a generated column.

DB2 9.7 supports XML parameters in user-defined function udfs, allowing you to define generated columns and automatically fill in values in the inserted XML document. The following code shows a UDF that accepts an XML document as input. For example, in the order document in the previous example, this UDF uses the XMLCAST and XMLQUERY functions to extract the OrderDate attribute of the input document:

 
 

  
  
CREATE FUNCTION extractDate(doc XML)  
  
  
  RETURNS DATE 
  
  
  LANGUAGE SQL CONTAINS SQL  
  
  
  NO EXTERNAL ACTION DETERMINISTIC  
  
  
  RETURN XMLCAST(XMLQUERY('$d/order/@OrderDate' 
  
  
         PASSING doc AS "d") AS DATE); 
 
 

You can use this UDF in SELECT queries and other SQL statements, but you also need to define a generated column. For the following example, it is assumed that retrieving and inserting a complete order is the most critical operation, in this case, it is best to store order documents completely. The following code defines a table that uses XML columns to store orders, and automatically extracts order dates and fills them in the associated column odate. An INSERT statement can now INSERT an XML document to the order column without the need to extract values to the associated column:

 
 

  
  
CREATE TABLE orders(  
  
  
  order XML,  
  
  
  odate DATE GENERATED ALWAYS AS (extractDate(order))); 
 
 

If you store many orders continuously, you may need to archive old orders. Using range partitioning is the best choice, the following code shows that table order2 is partitioned by the value of the odate column, and the odate column is generated from the XML column. Similarly, you can use the generated columns as the distribution key of the partitioned database or the cluster key of the MDC table:

 
 

  
  
CREATE TABLE order2(  
  
  
  order XML,  
  
  
  odate DATE GENERATED ALWAYS AS (extractDate(order)) NOT NULL)  
  
  
 PARTITION BY RANGE (odate)  
  
  
 (PART q109 STARTING('01-01-2009') ENDING ('03-31-2009') INCLUSIVE,  
  
  
 PART q209 ENDING ('06-30-2009') INCLUSIVE,  
  
  
 PART q309 ENDING ('09-30-2009') INCLUSIVE,  
  
  
 PART q409 ENDING ('12-31-2009') INCLUSIVE); 
 
 

Control XML storage

Custom XML storage has many advantages. Splitting large XML documents into multiple small documents makes it easier and more efficient to operate XML data, using UDF definition to generate columns can simplify the extraction of XML values to associated columns. Using the generated columns can also help you manage partition databases, range partition tables, or XML in MDC tables.

Source: http://www.ibm.com/developerworks/data/library/dmmag/DMMag_2009_Issue3/Tips/index.html

Original article: Customizing XML storage in DB2

Author: Matthias Nicla