DB2 FOR LUW / DB2FORLUW / 970 - DB2

A customer may encounter SQL0901N running an UPDATE or SELECT 
query. 
 
This happens if the query uses INNER JOIN instead of comma 
separated join and if one of the following is true: 
1. The INNER JOIN is referencing the same table multiple times. 
2. There is a subquery joining on a unique column 
 
The message displayed to the user can be "DOID1 is missing" or 
"OID imbalance". 
 
Example 1: 
 
 
*** 
select 
  1 
 
from 
  t1 
  join  t3 
    on   (t1.c1= t3.c1) 
  join  t4 
    on   (t3.c2= t4.c2) 
  join  t4 as t5 
    on   (t3.c2 = t5.c2) 
  join  t4 as t6 
    on   (t1.c2 = t6.c2) 
  join  t4 as t7 
    on   (t1.c1 = t7.c1) 
 
where 
     t1.c1 <> 1 ; 
 
 
can be rewritten as: 
 
select 
  1 
 
from 
  t1, t3, t4, t4 as t5, t4 as t6, t4 as t7 
where 
  (t1.c1= t3.c1) AND 
  (t3.c2= t4.c2) AND 
  (t3.c2 = t5.c2) AND 
  (t1.c2 = t6.c2) AND 
  (t1.c1 = t7.c1) AND 
 
  t1.c1 <> 1 ; 
 
**** 
 
Example 2: 
 
**** 
INSERT INTO T1 
    SELECT 
      v1.c1, v1.c2 
    FROM 
      v1 
      INNER JOIN t2 
        ON t1.c1=v1.c1 
      INNER JOIN t3 
        ON t3.c1 = t2.c1 
    WHERE v1.c4 IN 
              (SELECT c4 FROM 
 
                TABLE(FOO(123,'A')) AS table_function); 
 
can be rewritten as: 
 
INSERT INTO T1 
    SELECT 
      v1.c1, v1.c2 
    FROM 
      (SELECT c4 FROM 
                TABLE(FOO(123,'A')) AS table_function) as SQ 
      INNER JOIN v1 
      ON SQ.c4 = v1.c4 
      INNER JOIN t2 
        ON t1.c1=v1.c1 
      INNER JOIN t3 
        ON t3.c1 = t2.c1; 
 
If the C4 from the table function is NOT unique, this rewritten 
query is not equivalent to the original query and we CANNOT use 
this work-around. 
 
****

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* SQL0901N WHEN INNER JOIN IS USED IN AN UPDATE OR SELECT      * 
* QUERY.                                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 3.                       * 
****************************************************************

Replace the inner join with a comma separated join to fix the 
issue

DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 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 DB2 Version 9.7 Fix Pack 3.

N/A

N/A

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

20.04.2010
28.09.2010
28.09.2010

9.7.FP3