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 IC64326 Status: Geschlossen

PROJECT OUT UNNEEDED XML COLUMNS AND ENABLE INDEX ACCESS FOR XMLTABLE
JOIN PREDICATE

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
There are two issues that we solved: 
 
1. Eliminate unnecessary XML columns that are not used in any 
expression together with the corresponding operations/xpath that 
produces them. 
 
Typical customer scenario: 
 
CREATE VIEW CUSTSUPERVIEW AS 
SELECT T.* 
FROM CUSTACC, 
     XMLTABLE(' declare namespace 
c=http://tpox-benchmark.com/custacc; 
 
$CADOC/c:Customer/c:Accounts/c:Account/c:Holdings/c:Position' 
 COLUMNS 
         HOLDACCTID VARCHAR(12) PATH '../../@id', 
         HOLDCUSTID DOUBLE      PATH '../../../../@id', 
  HOLDSYM   VARCHAR(20) PATH '*:Symbol', 
   HOLDNAME          VARCHAR(50) PATH '*:Name', 
   HOLDTYPE          VARCHAR(20) PATH '*:Type', 
   HOLDQUANTITY      FLOAT       PATH '*:Quantity', 
   ACCTID      VARCHAR(12) PATH '../../@id', 
   ACCTTITLE         VARCHAR(50) PATH '../../*:AccountTitle', 
   ACCTCURRENCY      VARCHAR(5)  PATH '../../*:Currency', 
   ACCTONLINEBALANCE INTEGER     PATH 
'../../*:Balance/*:OnlineActualBal', 
   CUSTID    DOUBLE      PATH '../../../../@id', 
   CUSTSHORTNAME     VARCHAR(30) PATH 
'../../../../*:ShortNames/*:ShortName', 
   CUSTDATEOFBIRTH   DATE        PATH 
'../../../../*:DateOfBirth', 
   CUSTGENDER        VARCHAR(10) PATH '../../../../*:Gender', 
   CUSTNATIONALITY   VARCHAR(30) PATH 
'../../../../*:Nationality', 
   CUSTCOUNTRY     VARCHAR(30) PATH 
'../../../../*:CountryOfResidence') AS T; 
 
select custid from custsuperview; 
 
Only the xpath that defines custid column will be accessed. The 
other columns/xpaths are pruned by DB2 Rewrite component. 
 
2. Enable index usage for relational join predicates that 
references columns computed by XPath expressions, in particular 
XMLTABLE columns, by generating equivalent XML join predicates 
that Optimizer can use during index planning. 
 
Consider the following example: 
 
SELECT * 
FROM XMLTABLE('db2-fn:xmlcolumn("TEST1.DATA")' 
       columns col1 double path 'xmldata/a/b') as t1, 
     XMLTABLE('db2-fn:xmlcolumn("TEST2.DATA")' 
        columns col1 double path 'xmldata/a/c', 
                col2 double path 'xmldata/a/d') as t2 
WHERE t1.col1 = t2.col1; 
 
 
CREATE INDEX IX1 ON TEST1 (DATA ASC) GENERATE KEY USING 
XMLPATTERN '/xmldata/a/b' AS SQL double; 
CREATE INDEX IX2 ON TEST2 (DATA ASC) GENERATE KEY USING 
XMLPATTERN '/xmldata/a/c' AS SQL double; 
 
This work will enable index access IX1 or IX2 for the join 
predicate t1.col1 = t2.col2.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* XML feature in DB2; all platforms                            * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* There are two issues that we solved:                         * 
*                                                              * 
* 1. Eliminate unnecessary XML columns that are not used in    * 
* any                                                          * 
* expression together with the corresponding operations/xpath  * 
* that                                                         * 
* produces them.                                               * 
*                                                              * 
* Typical customer scenario:                                   * 
*                                                              * 
* CREATE VIEW CUSTSUPERVIEW AS                                 * 
* SELECT T.*                                                   * 
* FROM CUSTACC,                                                * 
* XMLTABLE(' declare namespace                                 * 
* c=http://tpox-benchmark.com/custacc;                         * 
*                                                              * 
* $CADOC/c:Customer/c:Accounts/c:Account/c:Holdings/c:Position' 
*                                                              * 
* COLUMNS                                                      * 
* HOLDACCTID VARCHAR(12) PATH '../../@id',                     * 
* HOLDCUSTID DOUBLE PATH '../../../../@id',                    * 
* HOLDSYM VARCHAR(20) PATH '*:Symbol',                         * 
* HOLDNAME VARCHAR(50) PATH '*:Name',                          * 
* HOLDTYPE VARCHAR(20) PATH '*:Type',                          * 
* HOLDQUANTITY FLOAT PATH '*:Quantity',                        * 
* ACCTID VARCHAR(12) PATH '../../@id',                         * 
* ACCTTITLE VARCHAR(50) PATH '../../*:AccountTitle',           * 
* ACCTCURRENCY VARCHAR(5) PATH '../../*:Currency',             * 
* ACCTONLINEBALANCE INTEGER PATH                               * 
* '../../*:Balance/*:OnlineActualBal',                         * 
* CUSTID DOUBLE PATH '../../../../@id',                        * 
* CUSTSHORTNAME VARCHAR(30) PATH                               * 
* '../../../../*:ShortNames/*:ShortName',                      * 
* CUSTDATEOFBIRTH DATE PATH                                    * 
* '../../../../*:DateOfBirth',                                 * 
* CUSTGENDER VARCHAR(10) PATH '../../../../*:Gender',          * 
* CUSTNATIONALITY VARCHAR(30) PATH                             * 
* '../../../../*:Nationality',                                 * 
* CUSTCOUNTRY VARCHAR(30) PATH                                 * 
* '../../../../*:CountryOfResidence') AS T;                    * 
*                                                              * 
* select custid from custsuperview;                            * 
*                                                              * 
* Only the xpath that defines custid column will be accessed.  * 
* The                                                          * 
* other columns/xpaths are pruned by DB2 Rewrite component.    * 
*                                                              * 
* 2. Enable index usage for relational join predicates that    * 
* references columns computed by XPath expressions, in         * 
* particular                                                   * 
* XMLTABLE columns, by generating equivalent XML join          * 
* predicates                                                   * 
* that Optimizer can use during index planning.                * 
*                                                              * 
* Consider the following example:                              * 
*                                                              * 
* SELECT *                                                     * 
* FROM XMLTABLE('db2-fn:xmlcolumn("TEST1.DATA")'               * 
* columns col1 double path 'xmldata/a/b') as t1,               * 
* XMLTABLE('db2-fn:xmlcolumn("TEST2.DATA")'                    * 
* columns col1 double path 'xmldata/a/c',                      * 
* col2 double path 'xmldata/a/d') as t2                        * 
* WHERE t1.col1 = t2.col1;                                     * 
*                                                              * 
*                                                              * 
* CREATE INDEX IX1 ON TEST1 (DATA ASC) GENERATE KEY USING      * 
* XMLPATTERN '/xmldata/a/b' AS SQL double;                     * 
* CREATE INDEX IX2 ON TEST2 (DATA ASC) GENERATE KEY USING      * 
* XMLPATTERN '/xmldata/a/c' AS SQL double;                     * 
*                                                              * 
* This work will enable index access IX1 or IX2 for the join   * 
* predicate t1.col1 = t2.col2.                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* N/A                                                          * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows

Lösung
DB2 V97FP1
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
03.11.2009
17.02.2010
17.02.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP1
Problem behoben lt. FixList in der Version
9.7.0.1 FixList