IBM Support

IC77880: AN INCORRECT COST ESTIMATION BY THE OPTIMIZER CAUSES AN INCORRECT PLAN TO BE PICKED, RESULTING IN POOR QUERY PERFORMANCE

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • This problem only happens when the query contains a constant in
    the range predicate specification.
       Below is an example of a query that will cause this problem:
    
    SELECT * FROM MPI.FRAGMENTARY_PATIENT
    WHERE (xmlexists('declare default element namespace
    "urn:hl7-org:v3";
                                   declare namespace
    axolotl="urn:axolotl-com:pdo";
                                   $c
    /ClinicalDocument/recordTarget/patientRole/patient[birthTime/@va
    lue[.
    >= $val1 and . <= $val2]]'
                                   PASSING  PATIENT_DATA AS "c",
                                     cast ('2010040' AS
    varchar(512)) AS "val1",
                                     cast ('2010040ZZZZZZ' AS
    varchar(512)) AS "val2")
           AND xmlexists('$c
    /ClinicalDocument/recordTarget/patientRole/patient
                                     [name3NameKey[(. >= $val1 and .
    <= $val2) or
                                                                (.
    >= $val3 and . <= $val4) or
                                                                (.
    >= $val5 and . <= $val6) or
                                                                (.
    >= $val7 and . <= $val8)]]'
                                   PASSING PATIENT_DATA_OPT AS "c",
                                     cast ('8666878962753636' AS
    varchar(512)) AS "val1",
                                     cast ('8666878962759090' AS
    varchar(512)) AS "val2",
                                     cast ('8988766263787062' AS
    varchar(512)) AS "val3",
                                     cast ('8988766263787066' AS
    varchar(512)) AS "val4",
                                     cast ('8666878736363636' AS
    varchar(512)) AS "val5",
                                     cast ('8666878736369090' AS
    varchar(512)) AS "val6",
                                     cast ('8666363636363636' AS
    varchar(512)) AS "val7",
                                     cast ('8666363636363647' AS
    varchar(512)) AS "val8"))
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Users on V9.7 FP4 and below                                  *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * AN INCORRECT COST ESTIMATION BY THE OPTIMIZER CAUSES AN      *
    * INCORRECT PLAN TO BE PICKED, RESULTING IN POOR QUERY         *
    * PERFORMANCE:                                                 *
    *                                                              *
    * This problem only happens when the query contains a constant *
    * in                                                           *
    * the range predicate specification.                           *
    *   Below is an example of a query that will cause this        *
    * problem:                                                     *
    *                                                              *
    * SELECT * FROM MPI.FRAGMENTARY_PATIENT                        *
    * WHERE (xmlexists('declare default element namespace          *
    * "urn:hl7-org:v3";                                            *
    *                               declare namespace              *
    * axolotl="urn:axolotl-com:pdo";                               *
    *                               $c                             *
    * /ClinicalDocument/recordTarget/patientRole/patient[birthTime *
    * /@va                                                         *
    * lue[.                                                        *
    * >= $val1 and . <= $val2]]'                                   *
    *                               PASSING  PATIENT_DATA AS "c",  *
    *                                 cast ('2010040' AS           *
    * varchar(512)) AS "val1",                                     *
    *                                 cast ('2010040ZZZZZZ' AS     *
    * varchar(512)) AS "val2")                                     *
    *       AND xmlexists('$c                                      *
    * /ClinicalDocument/recordTarget/patientRole/patient           *
    *                                 [name3NameKey[(. >= $val1    *
    * and .                                                        *
    * <= $val2) or                                                 *
    *                                                              *
    * (.                                                           *
    * >= $val3 and . <= $val4) or                                  *
    *                                                              *
    * (.                                                           *
    * >= $val5 and . <= $val6) or                                  *
    *                                                              *
    * (.                                                           *
    * >= $val7 and . <= $val8)]]'                                  *
    *                               PASSING PATIENT_DATA_OPT AS    *
    * "c",                                                         *
    *                                 cast ('8666878962753636' AS  *
    * varchar(512)) AS "val1",                                     *
    *                                 cast ('8666878962759090' AS  *
    * varchar(512)) AS "val2",                                     *
    *                                 cast ('8988766263787062' AS  *
    * varchar(512)) AS "val3",                                     *
    *                                 cast ('8988766263787066' AS  *
    * varchar(512)) AS "val4",                                     *
    *                                 cast ('8666878736363636' AS  *
    * varchar(512)) AS "val5",                                     *
    *                                 cast ('8666878736369090' AS  *
    * varchar(512)) AS "val6",                                     *
    *                                 cast ('8666363636363636' AS  *
    * varchar(512)) AS "val7",                                     *
    *                                 cast ('8666363636363647' AS  *
    * varchar(512)) AS "val8"))                                    *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Update to FixPack 5 or higher                                *
    ****************************************************************
    

Problem conclusion

  • First Fixed in DB2 V9.7 FP5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC77880

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-02

  • Closed date

    2011-12-21

  • Last modified date

    2011-12-21

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC77880

Modified date: 21 December 2011