DB2 FOR LUW / DB2FORLUW / A50 - 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

WRONG RESULTS OR SQL901N MAY OCCUR WHEN EXECUTING A SQL
STATEMENT CONTAINING TWO OR MORE NOT IN OR NOT EXISTS PREDICATES

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

When encountering this problem, you can apply the following
registry setting using an optimization guideline on the affected
queries as a workaround:

/*
     
      

     
*/;

not known / see Local fix

forerunner  : 
follow-up : IT22221 

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

05.06.2017
11.07.2018
16.07.2018