DB2 - Problembeschreibung
Problem IC71248 | Status: Geschlossen |
Possible incorrect result on recursive views which joins to a table on a unique column | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
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. | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows | |
Lösung | |
Fixed in DB2 Version 9.5 Fix Pack 6a. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71251 IC71254 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 17.09.2010 05.10.2010 05.10.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP6 | |
Problem behoben lt. FixList in der Version | |
9.1.0.7 | |
9.5.0.7 |