DB2 - Problembeschreibung
Problem IC62221 | Status: Geschlossen |
CORRELATED HIERARCHICAL QUERY RETURNS INCORRECT RESULT IN DPF | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
A correlated hierarchical query may return incorrect result in DPF in DB2. . Below is an example of a correlated hierarchical query: . SELECT CB.NAME, CB.CHAIN FROM MANAGERS M, TABLE(SELECT NAME, CONNECT_BY_ROOT NAME AS ROOT, SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN FROM MY_EMP START WITH NAME = M.NAME CONNECT BY PRIOR EMPID = MGRID ORDER SIBLINGS BY NAME) CB WHERE M.DEPT IN ('HR', 'FINANCE'); | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All DB2 Version 9.5 DPF systems on all Linux, Unix and * * Windows platforms. * **************************************************************** * PROBLEM DESCRIPTION: * * A correlated hierarchical query can return incorrect result. * * * * . * * Below is an example of a correlated hierarchical query: * * * * . * * SELECT CB.NAME, CB.CHAIN * * FROM MANAGERS M, * * TABLE(SELECT NAME, CONNECT_BY_ROOT NAME AS ROOT, * * SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN * * FROM MY_EMP START WITH NAME = M.NAME * * CONNECT BY PRIOR EMPID = MGRID * * ORDER SIBLINGS BY NAME) CB * * WHERE M.DEPT IN ('HR', 'FINANCE'); * **************************************************************** * RECOMMENDATION: * * Please refer to Local Fix section. * **************************************************************** | |
Local-Fix: | |
If possible rewrite the query to an equivalent one without a correlated hierarchical query: SELECT CB.NAME, CB.CHAIN FROM MANAGERS M, TABLE(SELECT NAME, CONNECT_BY_ROOT NAME AS ROOT, SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN FROM MY_EMP CONNECT BY PRIOR EMPID = MGRID ORDER SIBLINGS BY NAME) CB WHERE M.DEPT IN ('HR', 'Finance') AND M.NAME=CB.ROOT; . However, consider the performance implications of the rewritten query. For example, the above hierarchical query no longer has a START WITH clause. A START WITH clause on some other attribute is recommended to limit the number of rows produced bythe hierarchical query. | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows | |
Lösung | |
The complex fix for this problem first appears in DB2 Version 9.5 Fixpack 5 and all the subsequent fixpacks. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC62258 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 27.07.2009 16.02.2010 16.02.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP5 | |
Problem behoben lt. FixList in der Version | |
9.5.0.5 |