home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC95383 Status: Closed

QUERY WITH XQUERY/XPATH OPERATORS MAY RUN SLOWER WHEN UPGRADING FROM
VERSION 9.5.

product:
DB2 FOR LUW / DB2FORLUW / A50 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* 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  * 
****************************************************************
Local Fix:
available fix packs:
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

Solution
Fixed in DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4)
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
27.08.2013
18.09.2014
18.09.2014
Problem solved at the following versions (IBM BugInfos)
Problem solved according to the fixlist(s) of the following version(s)
10.5.0.4 FixList