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

CORRELATED HIERARCHICAL QUERY RETURNS INCORRECT RESULT IN DPF

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
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 Summary:
**************************************************************** 
* 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.
available fix packs:
DB2 Version 9.5 Fix Pack 5 for Linux, UNIX, and Windows
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
The complex fix for this problem first appears in DB2 Version 
9.5 Fixpack 5 and all the subsequent fixpacks.
Workaround
not known / see Local fix
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC62258 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
27.07.2009
16.02.2010
16.02.2010
Problem solved at the following versions (IBM BugInfos)
9.5.FP5
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.5 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.