DB2 FOR LUW / DB2FORLUW / 950 - DB2

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');

**************************************************************** 
* 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.                           * 
****************************************************************

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.

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
 

The complex fix for this problem first appears in DB2 Version 
9.5 Fixpack 5 and all the subsequent fixpacks.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC62258 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

27.07.2009
16.02.2010
16.02.2010

9.5.FP5