DB2 FOR LUW / DB2FORLUW / 980 - DB2

An SQL statement with a recursive view using UNION ALL that 
joins to a table on a set of columns that forms a unique key may 
return incorrect results. 
 
For example: 
 
CREATE TABLE RELATION(ID INTEGER, PARENTID INTEGER, FLAG 
CHAR(1)); 
 
-- This unique index is required to produce the incorrect 
results output 
CREATE UNIQUE INDEX UK1 ON RELATION(ID); 
 
CREATE VIEW HIRARCHY(ID, PARENTID, DEPTH) 
AS 
WITH TREE 
(ID, PARENTID, DEPTH) AS 
(SELECT ID, PARENTID, 1 AS DEPTH FROM RELATION ROOT 
 UNION ALL 
 SELECT CHILD.ID, TREE.PARENTID, DEPTH+1 FROM TREE, RELATION 
CHILD WHERE TREE.ID = CHILD.PARENTID) 
SELECT * FROM TREE 
; 
 
INSERT INTO RELATION VALUES (1,2,'Y'),(2,3,'N'); 
 
SELECT H.ID, H.PARENTID, DEPTH 
FROM HIRARCHY H,RELATION R 
WHERE H.ID = R.ID 
AND R.FLAG = 'Y'; 
 
The correct result is 2 rows. 
 
ID          PARENTID    DEPTH 
----------- ----------- ----------- 
          1           2           1 
          1           3           2 
 
DB2 incorrectly returns only the first row. This problem is 
first introduced in DB2 version 9.5 Fix Pack 6 and 9.7 Fix 
Pack 3.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 users                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* As described in the APAR problem description section.        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to V9.8 FP1.                                         * 
****************************************************************

db2set DB2COMPOPT=x,65536+y 
 
where x and y are the values currently set in the system. 
 
and restart the DB2 instance

DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

The problem was fixed in V9.8 FP4.

not known / see Local fix

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

17.09.2010
29.07.2011
29.07.2011

9.8.FP1,
9.8.FP4