DB2 FOR LUW / DB2FORLUW / A50 - 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.5                                         * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 10.5 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.5 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

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

not known / see Local fix

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

27.08.2013
27.02.2014
27.02.2014