Easier and Faster way to update XML fields – XML Multiple Update in DB2 for z/OS

XMLModify function is introduced in DB2 10 for z/OS to do sub-document update, without reparsing the whole document. There is a big performance saving if the change is small (say an attribute value) when comparing to the size of the document.

XMLModify function supports the following actions:

  • insert before
  • insert as first into
  • insert as last into
  • insert into (behave same as “insert as last into”)
  • insert after
  • delete
  • replace node
  • replace value of node

If you have not done so, I recommend you to read another IDUG Article (http://www.idug.org/p/bl/et/blogaid=421) to get some background on XMLModify function.

DB2 12 for z/OS introduces XML Multiple Update, which further enhances the XMLModify function that allow you to

  • determine the update action based on conditions
  • do multiple updates in one single XMLModify function call

by supporting FLOWR expression, conditional expression, and comma(sequence) expression inside XMLModify function. In the following sections, we will learn about these new features.

 

Pre-requisite:

  1. XML multi-versioning (i.e. Base table created in UTS in DB 11 NFM or later)
  2. DB2 12 for z/OS

 

Our Scenario

Suppose we have created a T1 table:

CREATE TABLE T1 (PO XML)#

(Please note that I am using # as SQL terminator in this article.)

And we insert an XML document (purchase order) into this table.

INSERT INTO T1 VALUES(
'<ipo:purchaseOrder
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:ipo="http://www.example.com/IPO"
 xmlns:pyd="http://www.examplepayment.com"
 orderDate="1999-12-01" pyd:paidDate="2000-01-07">
 
 <shipTo exportCode="1" xsi:type="ipo:UKAddress">
   <name>Helen Zoe</name>
   <street>47 Eden Street</street>
   <city>Cambridge</city>
   <postcode>CB1 1JR</postcode>
 </shipTo>
 <items>
   <item partNum="872-AA">                        
      <productName>Lawnmower</productName>          
     <quantity>1</quantity>                        
     <USPrice>149.99</USPrice>                    
      <shipDate>2011-05-20</shipDate>
   </item>
   <item partNum="945-ZG">
      <productName>Sapphire Bracelet</productName>  
       <quantity>2</quantity>                        
       <USPrice>178.99</USPrice>                      
      <comment>Not shipped</comment>
  </item>
</items>
</ipo:purchaseOrder>')#

 

FLWOR Expression

Suppose we want to increase the USPrice of each item by 10%. In prior release, if there are n items, user needs to write n UPDATE statements to achieve this. In DB2 12, we can use FLWOR expression as follows. As you may already know, FLWOR stands for FOR, LET, WHERE, ORDER BY, and RETURN.

UPDATE T1 SET PO = XMLMODIFY(
'declare namespace ipo="http://www.example.com/IPO";
 for $i in /ipo:purchaseOrder/items/item
 let $p := $i/USPrice
 where xs:decimal($p)>0
 return
   replace value of node $p with $p *1.1')#

 Note:

Namespace need to be declared
('declare namespace ipo="http://www.example.com/IPO";)
here, otherwise, DB2 will issue error as it cannot find any match to update.

The SQL statement is easy to understand. It loops through each item, when position in each item, it checks whether the USPrice is valid or not, if yes, update the USPrice with 10% more.

To see the result of the UPDATE, we can run the following SQL statement to look at the content of /ipo:purchaseOrder/items'.

SELECT XMLQUERY('declare namespace ipo="http://www.example.com/IPO";
 /ipo:purchaseOrder/items' passing PO)
FROM T1#

Output:

<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ipo="http://www.example.com/IPO" xmlns:pyd="http://www.examplepayment.com"><item partNum="872-AA"><productName>Lawnmower</productName><quantity>1</quantity><USPrice>164.989</USPrice><shipDate>2011-05-20</shipDate></item><item partNum="945-ZG"><productName>Sapphire Bracelet</productName><quantity>2</quantity><USPrice>196.889</USPrice><comment>Not shipped</comment></item></items>

  1 record(s) selected

As you can see, the USPrice for Lawnmower is changed from 149.99 to 164.989 and USPrice of Sapphire Bracelet is changed from 178.99 to 196.889.

If you are using DB2 LUW, you can achieve the same purpose using the following SQL statement:

UPDATE T1 SET PO = XMLQUERY(
'declare namespace ipo="http://www.example.com/IPO";
 transform
 copy $newinfo := $info
 modify
   for $i in $newinfo/ipo:purchaseOrder/items/item
   let $p := $i/USPrice
   where xs:decimal($p)>0
   return
     do replace value of $p with $p *1.1
 return $newinfo'
 passing PO as "info")#

As you may already know, DB2 LUW supports top level XQuery and SQL/XML. The example above is a SQL/XML example. From syntax perspective, DB2 LUW is closer to W3C XQuery Update Facility 1.0 by supporting transform expression consists of three clauses, denoted by the keywords copy, modify, and return. However, for replace and delete expression, W3C specifies (node|nodes) are required, but LUW will throw an exception when delete or replace expression contains “node” or “nodes”.

For examples, LUW will issue error for

do replace value of node $p with $p *0.9

Also, LUW requires do in insert, delete, and replace expression, which is not required by W3C.

From implementation point of view, for LUW, the updating expressions operate on the copied nodes created by the copy clause of the transform expression. Using the example above, a copy of the whole XML document is copied, and then apply the change. The XMLModify function in DB2 for z/OS applies the change to part of document that needed to be updated (that normally stored in ~16k record) without making a copy of whole document.

 

Conditional Expression

Conditional expression allow us to determine the action based on conditions. Suppose we want to adjust the price according to the order quantity. If the order quantity is more than one, we give a discount of 10%, otherwise, we increase the USPrice by 5%.

UPDATE T1
SET PO = XMLMODIFY(
 'declare namespace ipo="http://www.example.com/IPO";
  for $i in /ipo:purchaseOrder/items/item
  let $p := $i/USPrice
  let $q := $i/quantity
  where xs:decimal($p)>0 and xs:integer($q)>0
  return
  (
   if (xs:integer($q) > 1) then
     replace value of node $p with $p *0.9
   else
      replace value of node $p with $p *1.05 )')

Similar to previous example, we use a FOR loop to loop through all items, then we check the price and quantity. In the return clause, we check whether quantity is more than 1 or not and adjust the USPrice.

To see the result of the UPDATE, we can run the following SQL statement to look at the content of /ipo:purchaseOrder/items'.

SELECT XMLQUERY('declare namespace ipo="http://www.example.com/IPO";
 /ipo:purchaseOrder/items' passing PO)
FROM T1# 

Output:

<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ipo="http://www.example.com/IPO" xmlns:pyd="http://www.examplepayment.com"><item partNum="872-AA"><productName>Lawnmower</productName><quantity>1</quantity><USPrice>173.23845</USPrice><shipDate>2011-05-20</shipDate></item><item partNum="945-ZG"><productName>Sapphire Bracelet</productName><quantity>2</quantity><USPrice>177.2001</USPrice><comment>Not shipped</comment></item></items>                                                  

1 record(s) selected

As you can see, the USPrice for Lawnmower is changed from 164.989 to 173.23845 and USPrice of Sapphire Bracelet is changed from 196.889 to 177.2001.

If you are using DB2 LUW, you can achieve the same purpose using the following SQL statement:

UPDATE T1
SET PO = XMLQUERY(
 'declare namespace ipo="http://www.example.com/IPO";
  transform
  copy $newinfo := $info
  modify
    for $i in $newinfo/ipo:purchaseOrder/items/item
    let $p := $i/USPrice
    let $q := $i/quantity
    where xs:decimal($p)>0 and xs:integer($q)>0
    return
    (
      if (xs:integer($q) > 1) then
        do replace value of $p with $p *0.9
      else
        do replace value of $p with $p *1.05
     )
  return $newinfo'
  passing PO as "info")#

 

Comma (Sequence) Expression

We can separate each updating expression by comma(,) inside XMLModify function.

Suppose we want to increase the order quantity of partNum="872-AA" to 2, and delete the item with partNum="945-ZG".

Below is the SQL statement:

UPDATE T1 SET PO = XMLMODIFY(
'declare namespace ipo="http://www.example.com/IPO";
replace value of node
/ipo:purchaseOrder/items/item[@partNum="872-AA"]/quantity
  with xs:integer(2),
delete node
  /ipo:purchaseOrder/items/item[@partNum="945-ZG"]')

To see the result of the UPDATE, we can run the following SQL statement to look at the content of /ipo:purchaseOrder/items'.

SELECT XMLQUERY('declare namespace ipo="http://www.example.com/IPO";
/ipo:purchaseOrder/items' passing PO)
FROM T1#                                                                                                  

Output:

<items xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ipo="http://www.example.com/IPO" xmlns:pyd="http://www.examplepayment.com"><item partNum="872-AA"><productName>Lawnmower</productName><quantity>2</quantity><USPrice>173.23845</USPrice><shipDate>2011-05-20</shipDate></item></items> 
1 record(s) selected

As you can see, the quantity of Lawnmower is changed from 1 to 2 and the second item, Sapphire Bracelet with partNum="945-ZG" is deleted.

If you are using DB2 LUW, you can achieve the same purpose using the following SQL statement:

UPDATE T1 SET PO = XMLQUERY(
 'declare namespace ipo="http://www.example.com/IPO";
  transform
  copy $newinfo := $info
  modify
   (do replace value of  
     $newinfo/ipo:purchaseOrder/items/item[@partNum="872-AA"]/quantity
      with xs:integer(2),
    do delete
      $newinfo/ipo:purchaseOrder/items/item[@partNum="945-ZG"]
    )
  return $newinfo'
  passing PO as "info")#

 

Performance

Very impressive result!

(testing under controlled environment)

  • There is no performance regression reported on single update
  • 1MB document with 5 Replaces : up to 86% improvement in both ET and CPU time
  • 10MB document with 10 deletes: up to 86% improvement in ET; 71% improvement in CPU time
  • Validation testing (i.e. apply XMLModify function on an XML column with one or more than one XML schema): 10MB document with 10 deletes: up to 94% improvement in ET; 81% improvement in CPU time.
  • FLWOR testing: 1MB, each with 8 target: up to 90% improvement in ET; 97% improvement in CPU time

 

Summary

We have discussed how to use FLWOR expression, conditional expression, and comma (sequence) expression inside XMLModify function to do multiple update in DB2 12 for z/OS. These new features not only improve developer productivity, but also allow us to update an XML document with great performance saving.

 

Acknowledgments

Thanks to Jae Lee for his assistance in executing and verifying the LUW syntax.

Recent Stories
DB2 12 In-Memory Feature: Fast Index Traversal

Looking at the DB2 12 Enhanced Merge testing

On the waves of DB2 12 for z/OS