IBM Support

IC71704: DATE PREDICATES INCORRECTLY OPTIMIZED IN QUERY REWRITE.

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • During query rewrite a date predicate is rewritten incorrectly.
    The Date arithmetic is not properly applied and the resulting
    optimized statement is not syntactically equivalent to the
    original query.
    
    For an example:
    
    Original Statement:
    ------------------
    select f02date
    from datetbl
    where (f02date + 2 month) = '2004-02-29'
    
    
    Optimized Statement:
    -------------------
    SELECT Q1.F02DATE AS "F02DATE"
    FROM MYSCHEMA.DATETBL AS Q1
    WHERE (Q1.F02DATE = '12/29/2003')
    
    As a result the return set will not contain rows that match the
    predicate.
    

Local fix

  • Rewriting the query by placing all constant terms on one side of
    the predicate might avoid the problem.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users In V97                                             *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * During query rewrite a date predicate is rewritten           *
    * incorrectly.                                                 *
    * The Date arithmetic is not properly applied and the          *
    * resulting                                                    *
    * optimized statement is not syntactically equivalent to the   *
    *                                                              *
    * original query.                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    * For an example:                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    * Original Statement:                                          *
    *                                                              *
    * ------------------                                           *
    *                                                              *
    * select f02date                                               *
    *                                                              *
    * from datetbl                                                 *
    *                                                              *
    * where (f02date + 2 month) = '2004-02-29'                     *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    *                                                              *
    * Optimized Statement:                                         *
    *                                                              *
    * -------------------                                          *
    *                                                              *
    * SELECT Q1.F02DATE AS "F02DATE"                               *
    *                                                              *
    * FROM MYSCHEMA.DATETBL AS Q1                                  *
    *                                                              *
    * WHERE (Q1.F02DATE = '12/29/2003')                            *
    *                                                              *
    *                                                              *
    *                                                              *
    * As a result the return set will not contain rows that match  *
    * the                                                          *
    * predicate.                                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * We recomend applying FP4. It might be possible to rewrite    *
    * the query to avoid the issue                                 *
    ****************************************************************
    

Problem conclusion

  • Problem fixed first in FP4
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC71704

  • 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

    2010-10-05

  • Closed date

    2011-05-19

  • Last modified date

    2011-05-19

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

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

    IC71959 IC71961

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP

  • R970 PSY

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC71704

Modified date: 19 May 2011