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 IC71248 Status: Closed

Possible incorrect result on recursive views which joins to a table on a
unique column

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
A SQL statement with a recursive view may yield incorrect 
results. The SQL statement uses UNION ALL joining the table onto 
a set of columns that forms a unique key. The joined table must 
be the table used in the recursive view. 
 
 
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 version 9.5 Fix Pack 6 returns only the first row in this 
example which is incorrect. 
 
This problem is first introduced in DB2 version 9.5 Fix Pack 6.
Problem Summary:
Code defect has been corrected in a fix pack release.
Local Fix:
If you do not have the registry variable DB2COMPOPT set, 
Perform the following action: 
db2set DB2COMPOPT=,65536 
Please note the comma preceding the number 
 
If you already have the registry variable set to DB2COMPOPT=x,y 
where  x and y are numbers that may be present together or by 
themselves 
then perform the following with appropriate values replacing x 
and z 
db2set DB2COMPOPT=x,z 
where z=65536 + y 
 
and then restart the DB2 instance.
available fix packs:
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
Fixed in DB2 Version 9.5 Fix Pack 6a.
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC71251 IC71254 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
17.09.2010
05.10.2010
05.10.2010
Problem solved at the following versions (IBM BugInfos)
9.5.FP6
Problem solved according to the fixlist(s) of the following version(s)
9.1.0.7 FixList
9.5.0.7 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.