DB2 FOR LUW / DB2FORLUW / 970 - DB2

XQueries using "let" expressions which aggregate large xml 
sequences and apply some predicate on that sequence give slower 
performance in v95, v97 when compared with v91. 
 
The query: 
 
declare default element namespace "http://xxxx/ 
namespace/portal/ap0021/zc_xdb_ap0021.xsd"; let 
$Source:=db2-fn:xmlcolumn("ZC_AP0021.DATA")[1] return 
<root>{$Source}</root> 
 
The performance impact comes from the following "let" 
expression: 
 
let $Source:=db2-fn:xmlcolumn("ZC_AP0021.DATA") 
 
The problem here is that we collect  ALL xml documents found in 
DATA column into one huge sequence. Afterwards we apply 
positional predicate of [1] to every item in the large sequence 
to only fetch the first item. The performance hit is NOT coming 
from the positional predicate itself but from the fact that we 
are applying it to every item in that sequence. Applying any 
other kind of predicate to these kinds of sequences will give 
similar performance results. 
 
The performance for such queries is bad in all releases and we 
advice against writing similar queries but the performance for 
these kinds of queries appears to be worst starting v95 GA 
onwards. This is because performance improvements such as 
Multiple Extractions (MEP) and Predicate Pushdown where 
delivered which changed a large portion of the XML engine, from 
query compiler, optimizer to XML Navigation runtime. As side 
effect of these features was how XML Navigation changed the way 
it generates empty tuples to represent empty sequences which 
will have an impact for queries like the one above.

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 LUW ALL                                                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to v97 FP7 or newer.                                 * 
****************************************************************

Rewrite query more efficiently so that it only fetches first row 
using sqlxml (xmltable). 
 
select 
xmlquery('declare default element namespace 
"http://xxxx/namespace/portal/ap0021/zc_xdb_ap0021.xsd"; let 
$ReceivedOrder := $newdoc return <root>{$ReceivedOrder}</root>' 
passing DOC as "newdoc") 
from 
(select T.DOC from 
 ZC_AP0021, 
 XMLTABLE (XMLNAMESPACES(DEFAULT 
'http://xxxx/namespace/portal/ap0021/zc_xdb_ap0021.xsd'), 
'$d' passing DATA as "d" 
    COLUMNS 
      DOC XML PATH '.') AS T 
FETCH FIRST ROW ONLY 
)@

DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 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
 

Fix is available in v97 FP7 and newer.

not known / see Local fix

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

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

26.03.2012
06.05.2014
06.05.2014

9.7.FP7