DB2 FOR LUW / DB2FORLUW / 970 - DB2

If more than one XMLTABLE columns are selected and there is a 
predicate on the XMLTABLE in the where clause, then incorrect 
results might be returned. 
 
In following query columns NAME and AREA in the same XMLTABLE 
(T2 in this case) are filtered by equal predicates in 
"where" clause and some records might be omitted in the final 
results: 
 
select T2.NAME, T2.AREA from MOVE, 
  XMLTABLE('$MOVEINFO/listing/locations/building' Columns 
    "DEPT" VARCHAR(15) PATH '@dept') 
  as T1, 
  XMLTABLE('$MOVEINFO/listing/items/item' Columns 
    "NAME" VARCHAR(20) PATH 'name', 
    "AREA" VARCHAR(10) PATH 'area') 
  as T2 
  where T2.NAME = 'laser printer' and T2.AREA = 'common' 
 
As a best practice, it is better to not put the XMLTABLE 
predicate in "where" clause, as the original query does. Use the 
XPATH predicate instead. After pushing down the predicate to 
XPATH, the predication can be done when doing navigation, which 
generally improves performance.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7.0.10.                             * 
****************************************************************

A) Pushing down predicates to XPATH is a recommended solution. 
sample predicates from one of where clause: 
  where T2.NAME = 'laser printer' 
can be pushed down to XPath instead: 
  XMLTable('$MOVEINFO/listing/items/item[name="laser printer"]' 
Same for 'AREA'. 
 
B) Disabling Multiple Extraction Points (MEP) using db2set 
variable: 
db2set DB2_COMPILER_XML_OVERRIDES=1 
db2stop force && db2start

The problem is first fixed in DB2 version 9.7.0.10.

not known / see Local fix

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

07.05.2014
10.11.2014
17.02.2015

9.7.0.10