DB2 FOR LUW / DB2FORLUW / A50 - DB2

DB2 might produce SQL0901N when the following conditions are 
true: 
1. There is a NOT IN subquery 
2. The NOT IN subquery contains one or more scalar subqueries 
which have correlation 
3. The parent of the NOT IN subquery, i.e. the column on which 
NOT IN predicate is applied is nullable 
4. There is another table in the query which is joined to the 
parent table 
 
eg. 
create table X(col1 int, col2 int); <--------- condition 3 
 
SELECT 
col1 
FROM 
X,  <-------------- parent table 
Y   <-------------- condition 4 
WHERE 
X.col1 = Y.col1 AND 
X.col1 NOT IN (         <----------------- condition 1 
        SELECT 
        col1 
        FROM 
        A, D 
        WHERE 
        A.col2 = D.col2 AND 
        A.col3 = D.col3 AND 
        D.col4 = (  SELECT   <----------------- condition 2 
                         MAX(col4) 
                         FROM  E 
                         WHERE 
                         E.col2 = D.col2  ) 
        ); 
 
The stack trace might be as follows: 
0        ossDumpStackTraceInternal 
1        ossDumpStackTraceV98 
2        OSSTrapFile::dumpEx 
3        sqlo_trce 
4        sqloDumpDiagInfoHandler 
5        __invoke_dynamic_linker__ 
6        pthread_kill 
7        sqloDumpEDU 
8        sqldDumpContext 
9        sqlrr_dump_ffdc 
10       sqlzeDumpFFDC 
11       sqlzeSqlCode 
12       sqlnn_erds 
13       sqlng_build_INDX_key                    ---->>> 
SQL0901N 
14       sqlng_build_KEY_obj 
15       sqlng_build_INDX_obj

**************************************************************** 
* USERS AFFECTED:                                              * 
* All db2 v10.5 users                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v10.5 fp4 or higher                           * 
****************************************************************

db2set DB2_ANTIJOIN=YES

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
 

Fixed in DB2 v10.5 fp4

not known / see Local fix

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

04.03.2014
15.09.2014
15.09.2014