DB2 FOR LUW / DB2FORLUW / A50 - 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:                                              * 
*  Upgrade to DB2 Cancun Release 10.5.0.4 (also known as Fix * 
* Pack 4) or higher  * 
****************************************************************

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

Fixed in DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4)

not known / see Local fix

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

27.08.2013
18.09.2014
18.09.2014