DB2 FOR LUW / DB2FORLUW / A50 - DB2

A query might return incorrect results when the query has the 
following pattern: 
1. The query contains a subquery block, called subQ1, that 
references a base table BT1, called that quantifier Q1, and 
another subquery, called subQ2 
2. The subQ2 block references the same base table BT1. Call that 
quantifier Q2. 
3. The subQ2 block has an OR predicate, called orPrd, qualifying 
all the following conditions: 
3.a. The orPrd predicate takes the form (orPrd1 OR orPrd2 [OR 
...]).  One example could be an orPD predicate with at least two 
OR subterms denoted as orPrd1 and orPrd2. 
3.b. The orPrd predicate references column colX from Q1, and 
orPrd2 references column colX from Q2. In other words, orPrd1 is 
a correlation predicate, orPrd2 is local predicate, and their 
referenced columns derive from the same base table column. 
3.c. The orPrd1 and orPrd2 predicates are exactly matched or 
subsume each other. For example, Q1.colX=1 and Q2.colX = 1 are 
exactly matched, Q1.colX in (1,2) is subsumed by Q2.colX in 
(1,2,3). 
4. The query is compiled at optimization level 5 or above. 
 
The symptom is in the optimized statement when after either the 
orPrd1 or orPrd2 predicates are removed.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users on DB2 V10.5 FP3 and earlier                       * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.5.0.3.                             * 
****************************************************************

Reduce the query optimization level to below 5.  For example, 
issue the "db2 set current query optimization 2" command to 
reduce optimization level to 2.

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
 

The problem is first fixed in DB2 version 10.5.0.3.

not known / see Local fix

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

16.12.2013
28.02.2014
07.04.2015