home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
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
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IT01616 Status: Geschlossen

QUERIES WITH XMLTABLE FUNCTIONS MIGHT RETURN INCORRECT RESULTS WHEN MORE
THAN ONE EQUAL PREDICATE IS USED IN WHERE CLAUSE

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7.0.10.                             * 
****************************************************************
Local-Fix:
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
Lösung
The problem is first fixed in DB2 version 9.7.0.10.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
07.05.2014
10.11.2014
17.02.2015
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.0.10
Problem behoben lt. FixList in der Version
9.7.0.10 FixList