DB2 FOR LUW / DB2FORLUW / 970 - DB2

We may get the following error while creating a query or stored 
procedure containing a query having a correlated predicate that 
generates data partition elimination ranges. The error message 
recieved by the statement  may look like the following: 
 
 
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned: 
 
SQL0901N  The SQL statement failed because of a non-severe 
system error. Subsequent SQL statements can be processed. 
(Reason 
"sqlno_datapart_conv_key [140]:rc(     0)                ".) 
LINE NUMBER=4. 
SQLSTATE=58004 
 
The conditions to fall into this issue are : 
 
1. the query has a correlated predicate 
2. this correlated predicate is an OR predicate 
3. the subterms of the OR predicate contains columns that 
    can be used for data partition elimination 
 
For example, this is a correlated predicate: 
(SELECT COUNT(*) FROM MYSCHEMA.MYTABLE WHERE DATE_1 < T1.DATE_1 
OR (DATE_1 = T1.DATE_1 AND TIME_1 <= T1.TIME_1)) 
 
From the above, 2 ranges will be generated: 
1) DATE_1 < T1.DATE_1  (stop key) 
2) DATE_1 = T1.DATE_1  (start/stop key) 
 
Another potential error message received is -901 "Unexpected 
predicate type": 
 
FUNCTION: DB2 UDB, relation data serv, sqlrr_dump_ffdc, 
probe:300 
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 26 
 sqlerrmc: Unexpected predicate type. 
 sqlerrp : SQLNO40C 
 sqlerrd : (1) 0x00000000      (2) 0x00000000      (3) 
0x00000000 
           (4) 0x00000000      (5) 0xFFFFFF74      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11)

**************************************************************** 
* USERS AFFECTED:                                              * 
* All                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* see Error Description above                                  * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* upgrade to version 9.7 fixpack 4                             * 
****************************************************************

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 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
 

First fixed in version 9.7 fixpack 4

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC90765 
follow-up : 

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

29.07.2010
28.04.2011
13.03.2013

9.7.FP4