DB2 FOR LUW / DB2FORLUW / 970 - DB2

In some rare combination, queries with XQuery/XPath expressions 
may run slower when 
upgrading from version 9.5. 
 
Below are the characteristics of the queries that might 
encounter this problem: 
 
1. The query consists of a join predicate between a column from 
a relational table and a column 
    passing from XML documents. 
2. The access plan of the query shows that the above join 
predicate is rewritten to be part of 
    the extraction in an XQuery operator, typically seen as an 
XSCAN or XISCAN in the Explain 
    output. 
 
An example is: 
 
SELECT ... 
FROM    Rel_table as T 
WHERE ... IN 
            (SELECT ... 
             FROM    XML_table as XSRC, 
                          XMLTABLE('$d/DOC' PASSING X.XMLCOL AS 
"d" COLUMNS 
                            name VARCHAR(100) PATH 'BOOK/NAME', 
                            isbn   VARCHAR(15) PATH 'BOOK/ISBN') 
as XOUTPUT 
              WHERE ..  IN 
                           (SELECT ... 
                            FROM    ... 
                            WHERE  T.ISBN = XOUTPUT.ISBN 
                           ) 
             ) 
 
In the example above, the join predicate T.ISBN = XOUTPUT.ISBN 
is a join between a relational 
source and a column retrieved from XML documents. 
 
3. The join predicate is a correlated predicate. From the 
example above, both columns, T.ISBN 
   and XOUTPUT.ISBN are from the outer layer of the subselect. 
 
In most cases, an optimal access plan is to start with the 
relational table T to drive the execution 
of the inner <subselect> including the extraction from the XML 
documents. It is generally beneficial 
to push the join predicate between the relational table and the 
output from XML documents to be 
part of the extraction during the processing of the XML 
documents. However, this optimization is not 
optimal if T is a large table. has no local predicates that 
filter the rows, the access of the T could be 
more costly than a plan that extracts the column from the XML 
documents then joins to the table T.

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users using XQuery/XPath expressions                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Please refer Error Description to see the details            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Users can upgrade to DB2 Version 9.7 fix pack 9 or higher to * 
* avoid this defect                                            * 
****************************************************************

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
 

First Fixed in DB2 Version 9.7 fix pack 9

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC95283 IC95383 IC96263 
follow-up : 

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

26.03.2013
17.12.2013
17.12.2013

9.7.FP9