DB2 FOR LUW / DB2FORLUW / B10 - DB2

You might encounter wrong results or SQL901N errors when 
executing a SQL 
statement containing two or more NOT IN (or NOT EXISTS) 
predicates under the following conditions, where 
PRD_NOTIN_A and PRD_NOTIN_B are two 
eligible NOT IN predicates: 
 
1. The columns on the left hand side of PRD_NOTIN_A and 
PRD_NOTIN_B are from different tables; 
2. The table containing the column on the left hand side of 
PRD_NOTIN_A also contains column(s) used in another predicate 
    (Let's call it PRD_NON_NOT_IN); 
3. PRD_NON_NOT_IN has a sub-query that contains a correlated 
column with the column on the left hand side of PRD_NOTIN_B. 
 
Here is an example of a failing query: 
 
SELECT a1 
FROM   a 
WHERE  a.a2 NOT IN (SELECT b1 
                    FROM   b) 
//PRD_NOTIN_B 
       AND EXISTS (SELECT c1 
                   FROM   c 
                   WHERE  c.c1 = (SELECT d1 
                                  FROM   d 
                                  WHERE  d.d2 = a.a1) 
//PRD_NON_NOT_IN 
                          AND c.c1 NOT IN (SELECT e1 
                                           FROM   e)); 
//PRD_NOTIN_A

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Please, See Error Description and install this fix.          * 
****************************************************************

When encountering this problem, you can apply the following 
registry setting using an optimization guideline on the affected 
queries as a workaround: 
<query> 
/* 
     <OPTGUIDELINES> 
      <REGISTRY><OPTION NAME='DB2_ANTIJOIN' VALUE='NO'/> 
</REGISTRY> 
     </OPTGUIDELINES> 
*/;

Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

Please, See Error Description and install this fix.

not known / see Local fix

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

31.08.2017
16.03.2018
16.03.2018