DB2 FOR LUW / DB2FORLUW / 970 - DB2

Using large global sequences in XML queries can result in 
suboptimal performance due to intermediate result processing 
required to manage these large sequences during query 
processing. 
 
Queries susceptible to this type of performance typically start 
with a "let" clause and assign the sequence of all XML 
documents in a given table to some variable. 
 
This variable which is bound to the large sequence is then used 
to extract sequence item values, perform some kind of document 
construction or used to do predicate evaluation which applies 
to every item in the sequence. 
 
For any of these operations which perform some type of item 
extraction or evaluation on sequence items there is an 
underlying amount of intermediate processing required to 
process the entire xml sequence object. The larger the 
sequence, the more work required. The fix for this APAR helps 
improve performance for processing intermediate results from 
XML sequences. 
 
 
Example query which results in sub-optimal performance: 
 
xquery 
let $allitems := 
  ( for $i in db2-fn:xmlcolumn("PURCHASEORDER.PORDER") 
    return $i/PurchaseOrder/item ) 
for $pid in distinct-values($allitems/partid) 
order by $pid 
return 
<prod_info product = "{$pid}"> 
  <name>{distinct-values($allitems[partid = $pid]/name)} 
    </name> 
  <price>{distinct-values($allitems[partid = $pid]/price)} 
    </price> 
</prod_info>; 
 
In this example the query starts with a "let" clause and 
assigns sequence of all purchase order items from the table to 
$allitems. Unless the table is small, the sequence in 
$allitems is typically very large. Using "let" to combine items 
from all documents in the entire table often results in 
suboptimal performance. 
 
Next step we iterate for every distinct part value of all the 
item elements in the sequence $allitems. For each distinct 
"partid" it returns a constructed xml document "prod_info". 
Note that for each partid $pid, the query probes back in the 
sequence $allitems to find all items with matching partid. Same 
is done for other element values like "price". This type of 
coding is not straightforward and complex and can produce large 
amounts of intermediate results that need to be later 
processed for duplicate elimination. 
 
Same results can be achieved by using a much simpler query like 
the sql/xml query using XMLTABLE below: 
 
select 
XMLELEMENT ( 
  NAME "prod_info", 
  XMLATTRIBUTES( T2.pid as "product"), 
  XMLFOREST(T2.name as "name", 
            T2.price as "price")) 
FROM 
( SELECT distinct T.pid, T.name, T.price 
  FROM purchaseorder, 
  XMLTABLE('$PODER/PurchaseOrder/item' passing PORDER as 
     "PODER" 
     COLUMNS 
       pid    VARCHAR(10)    PATH  'partid', 
       name   VARCHAR(40)    PATH  'name', 
       price  VARCHAR(10)    PATH  'price') as T ) as T2 ; 
 
Writing queries in this manner avoids all the overhead of 
processing large intermediate results which comes from working 
with large XML sequences. Not only is it much more simple and 
easier to understand, but when compared to previous example 
this query performs several orders of magnitude faster.

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 9.7                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 FixPack 8.                            * 
****************************************************************

Use SQL/XML to rewrite bad performing XQuery. See Error 
Description for more details.

DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 UDB Version 9.7 FixPack 8.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC89702 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

20.06.2012
02.04.2013
02.04.2013

9.7.FP8