DB2 FOR LUW / DB2FORLUW / A10 - DB2

Under rare scenarios, DB2 might produce SQL0901N if the 
following conditions are true: 
1) The query block has a group by with one or more aggregation 
functions of the form MAX, MIN and SUM. 
2) One or more columns referenced in the aggregate functions are 
also used in a predicate in the null value rejecting context. 
3) The same columns are also referenced in a correlated subquery 
containing two or more tables 
4) There is an index on a subset of these columns. 
5) The access plan for the query shows a early out nested loop 
join where the subquery is applied in the INNER scan as residual 
predicate 
 
Sample query: 
SELECT * 
FROM  T1, 
               (SELECT Max(c2)   c2_max 
                FROM   (SELECT t2.c1, t2.c2 
                            FROM    t2, 
                                        t3 
                            WHERE  t3.c3 = t2.c3 
                               AND t2.c3 = ? 
                               AND t2.c2 = t3.c2 
                               AND NOT EXISTS (SELECT 1 
                                                          FROM 
 
t2 as t4, t5 
                                                          WHERE 
 
t4.c3 = t5.c3 AND t2.c2 < t4.c3)) 
                            GROUP  BY c1) tmp 
WHERE  t1.c2 = tmp.c2_max; 
 
Error message: 
SQL0901N  The SQL statement or command failed because of a 
database system error. (Reason "got a cond_isnotnull after 
building outer".)  SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* Users running DB2 on 10.1 before Fix Pack 5                  * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 10.1 Fix Pack 5                               * 
****************************************************************

db2set DB2_REDUCED_OPTIMIZATION=NO_NLJN_EO_FOR_GB

not known / see Local fix

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

08.08.2014
30.07.2015
30.07.2015