DB2 FOR LUW / DB2FORLUW / 970 - DB2

Query execution may terminate abnormally with an SQL0901N or an 
SQL0183N if the following conditions are satisfied: 
1. The query contains 2 or more instances of LEFT or RIGHT OUTER 
JOIN (LOJ) 
2. One LOJ is nested within another 
3. The join predicate(s) of the outer LOJ: 
a. are all equi-join predicates, where the operands are columns, 
i.e., not function expressions 
b. reference only columns from the row-preserving and 
null-producing sides, i.e., no constants, host-variables, etc. 
c. the columns from the null-producing side are from the 
application of the inner LOJ 
4. The join predicate(s) of the inner LOJ: 
a. contain at least one reference to a not-nullable column from 
the row-preserving side 
b. the not-nullable column is used inside possibly nested 
function expression(s) 
 
An example scenario: 
-- tables 
create table t1 (pid int); 
create table t2 (sid int not null, pid int); 
create table t3 (char_sid char(11)); 
 
-- query 
select    t1.pid, 
          t2.sid 
from      t1 
left join t2 
left join t3 
on        char(coalesce(t2.sid, 0)) <= t3.char_sid 
 
on        t1.pid = t2.pid 
;

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* The issue is due to internal missing nullability             * 
* synchronization after Outer Join linearization.              * 
* The fix is to propagate the nullability through consumer     * 
* function PIDs.                                               * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 4.                       * 
****************************************************************

Manually rewrite any problematic queries to preclude the 
conditions described above, e.g., for the query above, this can 
be done like so: 
select    t1.pid, 
          t2.sid 
from      t1 
left join t2 
on        t1.pid = t2.pid 
left join t3 
on        char(coalesce(t2.sid, 0)) <= t3.char_sid 
 
;

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
 

Problem was first fixed in DB2 Version 9.7 Fix Pack 4.

not known / see Local fix

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

13.12.2010
28.04.2011
28.04.2011

9.7.FP4