DB2 FOR LUW / DB2FORLUW / A10 - DB2

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

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

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.

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Fixed in DB2 v10.1 FP4.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC97615 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.10.2013
08.05.2014
26.05.2014