PI45691: QUERY WITH XML USING BETWEEN PREDICATES HAS UNEXPECTED HIGH NUMBER OF GETPAGES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• Query with XML using BETWEEN predicates has unexpected
  high number of getpages
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10, 11 for z/OS users of XML index.  *
  ****************************************************************
  * PROBLEM DESCRIPTION: The XML index access may return more    *
  *                      rows when the query includes predicates *
  *                      that are transformed into a "between"   *
  *                      predicate.                              *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The XML index access may return more rows when the query
  includes predicates that are transformed into a "between"
  predicate.
  
  The following DDL statements will create a table T1 with one
  XML column X1 and the XML index IDX11 to index on the value of
  node /a/b of the XML documents in column X1.
  
    CREATE TABLE T1 (X1 XML);
  
    CREATE INDEX IDX11 ON T1(X1)
      GENERATE KEYS USING
      XMLPATTERN '/a/b' AS SQL VARCHAR(10);
  
  In the following query, the predicate in the square bracket
  in the XPath will be transformed into "between" predicate.
  
    SELECT XMLSERIALIZE(X1 AS CLOB)
    FROM T1
    WHERE
      XMLEXISTS('$d/a/b . >= $p1 and . <= $p2 '
        PASSING X1 AS "d",
                CAST(? AS VARCHAR(10)) AS "p1",
                CAST(? AS VARCHAR(10)) AS "p2");
  
  A DB2 code bug that causes the upper bound of the "between"
  predicate not being set correctly, more rows then necessary
  may be returned.
  

Problem conclusion

• DB2 is modified, so the upper bound of the "between" predicate
  will be set correctly and the correct number of rows will be
  returned.
  
  Additional keywords: SQLXML XMLALL
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI30892 UI30893

Modules/Macros

• DSNNQTOP
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI30892

     UP15/09/24 P F509

• RB10 PSY UI30893

     UP15/09/24 P F509

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.