DB2 FOR LUW / DB2FORLUW / A10 - DB2

DB2 can trap when running an SQL statement that meets all of the 
conditions below: 
 
(1) The query fits the required criteria for zigzag join. Refer 
to the topic "Ensuring that queries fit the required criteria 
for the zigzag join" in DB2 InfoCentre for details about the 
criteria. 
(2) The dimension table is a derived table, that is, a table 
derived from a sub-query. 
(3) The sub-query refers to a column that is not in the query 
block that contains the zigzag join tables. 
 
For example, in the query below, Q18 is the query block that 
contains the zigzag join tables. Q11 is the dimension table and 
it refers to a column in Q21. 
 
 
SELECT ... 
FROM Q21, 
 (SELECT ... 
  FROM 
    (SELECT KEY2 
     FROM  Q9, Q10 
     WHERE 
       ... 
       AND Q21.C1 IS NOT NULL 
    ) AS Q11, 
    Q14, 
    Q15, 
  WHERE 
    (Q15.KEY1 = Q14.KEY1) AND 
    (Q15.KEY2 = Q11.KEY2) 
  ) AS Q18 
WHERE ... 
 
You can find the following functions in stack: 
 
sqlriZZDimMax1RuntimeP8sqlrr_cbP10sqlri_zzobtl + 0x007f 
sqlriZZDimFetchP8sqlrr_cbP10sqlri_zzobtltj + 0x0098 
sqlriZZOpenDimsP8sqlrr_cbP10sqlri_zzob + 0x0248 
sqlriZZInitP8sqlrr_cbP10sqlri_zzob + 0x003f 
sqlriZigZagJoinP8sqlrr_cb + 0x0288

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Problem Description above                                * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 10 Fix Pack 3                         * 
****************************************************************

db2set DB2_REDUCED_OPTIMIZATION="ZZJN OFF" 
OR 
Use optimization level 0 or 1

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

First fixed in DB2 Version 10 Fix Pack 3

not known / see Local fix

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

02.05.2013
17.10.2013
17.10.2013