DB2 FOR LUW / DB2FORLUW / A10 - DB2

Complex query is not fully simplified in situation when: 
 
- there is a check constraint on a specified column of the 
underlying table: 
 
ALTER TABLE CON1 
    ADD CONSTRAINT CON1 CHECK 
        (COL1 <= 201212 ) 
    ENFORCED 
    ENABLE QUERY OPTIMIZATION; 
 
 
- there is a mix of equality and range predicates in the various 
OR sub terms on the column involved in the check constraint. 
 
Query snippet: 
 
... 
(TAB1.COL1) BETWEEN 201301 AND 201309 AND TAB1.COL2 = 1 ) 
OR 
(TAB1.COL1 = 201301 AND TAB1.COL2 = 2 ) 
OR 
(TAB1.COL1 = 201309 AND TAB1.COL2 = 3 ) 
... 
 
This query is not fully simplified.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 Fix Pack 5                               * 
****************************************************************

Change equality predicates to range predicates 
 
Snippet: 
 
... 
(TAB1.COL1) BETWEEN 201301 AND 201309 AND TAB1.COL2 = 1 ) 
OR 
(TAB1.COL1 = 201301 AND TAB1.COL2 = 2 ) 
OR 
(TAB1.COL1 = 201309 AND TAB1.COL2 = 3 ) 
... 
 
can be changed to: 
 
... 
(TAB1.COL1) BETWEEN 201301 AND 201309 AND TAB1.COL2 = 1 ) 
OR 
(TAB1.COL1 >= 201301 AND TAB1.COL1 < 201302 AND TAB1.COL2 = 2 ) 
OR 
(TAB1.COL1 >= 201309 AND TAB1.COL1 < 201310 AND TAB1.COL2 = 3 ) 
...

First fixed in DB2 10.1 Fix Pack 5

not known / see Local fix

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

30.06.2014
17.02.2017
17.02.2017