PM48742: INCORRECT RESULT WHEN XML INDEX WITH KEY TYPE OF SQL DECFLOAT IS USED FOR XMLEXISTS.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• An incorrect result is returned when an XML index is used and
  the key type is SQL DECFLOAT and this index use is a result of
  processing the XMLEXISTS built in function.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 9 for z/OS and 10 for z/OS users of  *
  *                 pureXML.                                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: This APAR fixes two problems:           *
  *                      1.Incorrect output may be obtained when *
  *                        an XML value index with key type of   *
  *                        SQL DECFLOAT is used.                 *
  *                      2.SQLCODE -16061 is incorrectly issued  *
  *                        (on DB2 10) and incorrect output or   *
  *                        DSNXGRDS.DSNXEFDA:M120 abend may      *
  *                        occur (on DB2 9) for implicit casting *
  *                        of invalid format of special values   *
  *                        to numeric type in XPath predicates   *
  *                        for general comparison when no        *
  *                        matching index found.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR fixes two problems:
  1.Incorrect output may be obtained when an XML index with key
    type of SQL DECFLOAT is used. There are two possible causes:
    a. On DB2 9, there is a logic error in generating index key
       values for floating point data that conforms to the pattern
       (0-9)*.0+(1-9)+E... (* means 0 or more, + means 1 or more).
       In other words, the pattern has one or more 0s following .
       and then followed by non-zero digit(s) and then E.
       For example, wrong key values would be generated for
       1.05E20, 123.000078E-1 etc. but not for 1.00E3.
    b. NaN, INF and -INF are special values that are valid in XML
       value space while other variations such as nan, nAN, iNf,
       +NaN, -NaN, +INF are not valid. On DB2 9, decfloat index
       entries for valid special values INF and -INF are missed
       to be created. While on DB2 10, decfloat index entries for
       invalid format of INF such as inf, InF, +INF, etc. are
       wrongly created with the same value as INF and decfloat
       index entries for -iNf, -Inf etc. are also wrongly created
       with the same value as -INF.
  
    For example, an XML index with SQL DECFLOAT type is created
       CREATE TABLE T1 (ID INTEGER, DOC XML);
       CREATE INDEX INDX1 ON T1 (DOC)
  
       GENERATE KEY USING XMLPATTERN '/a/b' AS SQL DECFLOAT ;
  
    Suppose four rows are inserted
       INSERT INTO T1 (ID, DOC) VALUES(1,'<a><b>1.0E20</b></a>');
       INSERT INTO T1 (ID, DOC) VALUES(2,'<a><b>1.05E20</b></a>');
       INSERT INTO T1 (ID, DOC) VALUES(3,'<a><b>iNf</b></a>');
       INSERT INTO T1 (ID, DOC) VALUES(4,'<a><b>INF</b></a>');
  
    If user has a query below
       SELECT ID FROM T1
       WHERE XMLEXISTS('/a b>10000 ' PASSING DOC);
  
    The corrct output should have 3 rows returned with IDs 1,2 and
    4. On DB2 9, only 1 row is returned with ID 1. The ID 2 row is
    missing due to cause a and ID 4 row is missing due to cause
    b. On DB2 10, all 4 rows are returned with IDs 1,2,3 and 4.
    The ID 3 row is incorrectly output due to cause b.
  
  2.When performing general comparison of a node with a numeric
    value in XPath predicates while there is no matching index,
    implicit casting is carried out first to try to convert the
    node's value to numeric type. On DB2 9, DSNXGRDS.DSNXEFDA:M120
    may occur when the node's value is some invalid format of
    special values such as iNf, NAN. For some other invalid format
    such as +INF, it is treated as INF and incorrect output may be
    obtained. As stated above, only NaN, INF and -INF are valid
    special values in XML while other variations are invalid.
    On DB2 10, SQLCODE -16061 is incorrectly issued for implicit
    casting of invalid format of special values to numeric type
    in XPath predicates for general comparison. The correct way
    is to ignore invalid format of special values just as ignoring
    a random string when casting them to numeric type. In doing
    so, the query result is consistent with when there is a
    matching index ("aaa", "-NaN" are ignored at index creation
    time with no corresponding index entries created).
  
    Suppose we have the same DDL as above but the index INDX1 is
    dropped. For the same above query, DB2 9 gets
    DSNXGRDS.DSNXEFDA:M120 abend since the error handling is not
    handled correctly. DB2 10 issues SQLCODE -16061. The correct
    output should be consistent with when INDX1 is present.
  

Problem conclusion

• DB2 is changed to have correct decfloat index entries created
  for XML values; It is also changed to ignore the implicit
  casting error of invalid format of special values to XML numeric
  values when performing general comparison in XPath predicates.
  
  Additional Keywords: XMLALL XMLINDEX SQLDECFLOAT SQLXML
  INCORROUT DB2INCOR/K
  

Temporary fix

• *********
  * HIPER *
  *********
  

Comments

APAR Information

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

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

  UK79230 UK79231

Modules/Macros

• DSNNKGEN DSNNQDTM DSNNQIV  DSNNQOP  DSNNQOPM
  DSNNQOPN DSNNQOPR DSNNQOPS DSNNQRES DSNNQTOP DSNNQXCA
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UK79230

     UP12/06/15 P F206

• R910 PSY UK79231

     UP12/06/15 P F206

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.