DB2 FOR LUW / DB2FORLUW / A10 - DB2

DB2 may create unexpected optimized statement for view. 
 
bad/slow:  predicate is <case_exp> = <const> 
good/fast: predicate is <base_column> = <const> 
 
Rewrite rule CASEPRD changes the first predicate to, e.g., 
 
(((NULL = 'xxxxxx') AND 
    (Q1.AAAAAA = '')) OR 
   ((Q1.AAAAAA = 'xxxxxx') AND 
    (Q1.AAAAAA <> ''))) 
 = 'xxxxxx' 
 
Rule caseprd handles value NULL in the same way as regular 
constant and produce an equal predicate "(NULL = 'xxxxxx')".

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Version 10.1                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.1 FixPack 3.                           * 
****************************************************************

Change 
(case AAAAAA when '' then null else AAAAAA end) 
to 
(case when AAAAAA <> '' then AAAAAA end) 
 
i.e. avoid using NULL value explicitly as a THEN clause. 
 
Then, predicate "(case when AAAAAA <> '' then AAAAAA end) 
 = 'xxxxxx'" can be rewritten to "AAAAAA = 'xxxxxx'".

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

Problem was first fixed in DB2 UDB Version 10.1 FixPack 3.

not known / see Local fix

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

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

30.01.2013
01.10.2013
01.10.2013