Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC71254 Status: Closed

POSSIBLE INCORRECT RESULT ON RECURSIVE VIEWS JOIN TO A TABLE ON UNIQUE
COLUMN

product:
DB2 FOR LUW / DB2FORLUW / 980 - DB2
Problem description:
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.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* All DB2 users                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* As described in the APAR problem description section.        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to V9.8 FP1.                                         * 
****************************************************************
Local Fix:
db2set DB2COMPOPT=x,65536+y 
 
where x and y are the values currently set in the system. 
 
and restart the DB2 instance
available fix packs:
DB2 Version 9.8 Fix Pack 4 for AIX and Linux
DB2 Version 9.8 Fix Pack 5 for AIX and Linux

Solution
The problem was fixed in V9.8 FP4.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
17.09.2010
29.07.2011
29.07.2011
Problem solved at the following versions (IBM BugInfos)
9.8.FP1,
9.8.FP4
Problem solved according to the fixlist(s) of the following version(s)
9.8.0.4 FixList
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.