IBM Support

IC96725: CASE EXPRESSION PREDICATES MIGHT BE INCORRECTLY REWRITTEN in DB2 v10.1 Fixpack 3

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • A CASE expression predicate might be incorrectly rewritten.  The
    following conditions must be fulfilled to cause an incorrect
    case expression predicate to occur:
    1. The predicate is <CASE expression> IS NULL.
    2. The CASE expression is in the form of
    "CASE WHEN <col1> <relop1> <literal11> THEN <literal12> WHEN
    <col2> <relop2> <literal21> THEN <literal22> WHEN <col3>
    <relop3> <literal31> THEN <literal32> ... ELSE <colX> END".
    
    If N is any positive integer number, then <relopN> can be any
    comparison operator out of ("<", "<=", "<>", "=", ">=", ">") as
    well as IS NULL and IS NOT NULL. <literalNM> can be any
    constant, special register, host variable, or parameter marker.
    colN is a simple column. The same column can be repeated in WHEN
    clauses. colX from the ELSE clause must be also present in at
    least one WHEN clause. Also, colX must be defined as nullable.
    
    The symptom of the problem is the above predicate is mistakenly
    transformed to an OR predicate with a subterm that misses "colX
    IS NULL."  In those rows whose colX is NULL, this might result
    in those rows being incorrectly filtered by the rewrite
    predicate so the query block returns less rows.
    
    Running the NULLIF command might also lead to this problem.
    NULLIF(col1, <literal1>) is same as CASE WHEN <col1> =
    <literal1> THEN NULL ELSE <col1> END.
    
    If your database is an Oracle compatible database, using the
    predicate NULLIF(col,1") IS NULL will not have the CASE
    expression predicate problem.
    
    Issue will not occur in DB2 v10.1 Fixpack 2 and lower, but will
    affect DB2 v10.1 Fixpack 3
    

Local fix

  • 1. Change query optimization level to 0 or 1. The command is
    "db2 set current query optimization 0"
    2. If all columns in CASE expression are identical and all
    <relop> are "=", manually rewrite the CASE expression predicate
    to
    DECODE(colX, <literal11>, <literal12>, <lteral21>, <literal22>,
    <literal31>, <literal32>, ..., <colX>) IS NULL.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 v10.1 FP4.                                    *
    ****************************************************************
    

Problem conclusion

  • Fixed in DB2 v10.1 FP4.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC96725

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    A10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2013-10-08

  • Closed date

    2014-05-08

  • Last modified date

    2014-05-26

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

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

    IC97615

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RA10 PSN

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
26 May 2014