DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under rare scenarios, DB2 might produce SQL0901N error message 
if the following conditions are true: 
1. The query contains one or more table with generated columns. 
2. The generated column is not referenced in the output column 
list. 
3. The source of the generated column is used in a predicate 
4. There is a DISTINCT or a aggregation (DISTINCT) function in 
the output 
5. The query also contains a not exists or a not in subquery. 
 
Example: 
In the table EVENT DDL, 
"CREATIONDATE" DATE NOT NULL GENERATED ALWAYS AS 
(DATE(CREATIONTIME))  <------ condition 1 
 
SELECT COUNT(DISTINCT event.PROZESSID) AS ANZAHL, 
LEFT(CREATIONTIME,10) AS TAG    <------------ condition 2, 4 
from PMONITOR.EVENT as event 
WHERE event.CREATIONTIME >= '20120101000000' and 
event.CREATIONTIME < '20130101000000' AND   <--------- condition 
3 
NOT EXISTS( SELECT * FROM PMONITOR.EVENT as subEv WHERE 
subEv.EVENTNAME='VMDBREPLERGEBNIS' and 
subEv.PROZESSID=event.PROZESSID)   <------ condition 5 
GROUP BY LEFT(CREATIONTIME,10); 
 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed. 
(Reason "Order cols removed from sort output cols.".) 
SQLSTATE=58004 
 
The agent doing the query compilation will also produce a stack 
with a signature similar to: 
 
 sqlnn_cmpl[390] 
sqlno_exe[2000] 
sqlno_final_phase[100] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[200] 
sqlno_post_pass[1150] 
sqlno_post_order_near_norm[301] 
 
 
Function at the top of the stack to look for is 
sqlno_post_order_near_normalize

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
*                                                              * 
* Note this issue does not exist in V10.1 version of DB2 as    * 
* there are different query rewrite rules that get applied in  * 
* this version.                                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 and Fix Pack 9                    * 
****************************************************************

1. db2set DB2_ANTIJOIN=NO (Note this is only valid for MPP) 
2. Create redundant predicates in the query  '01/01/2012' <= 
event.CREATIONDATE and event.CREATIONDATE <= '01/01/2013'

DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 9.7 and Fix Pack 9

not known / see Local fix

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

14.03.2013
16.12.2013
16.12.2013

9.0.,
9.7.