DB2 FOR LUW / DB2FORLUW / 970 - DB2

DB2 can trap when compiling a select statement if the optimized 
select statement satisfies all of the conditions below 
 
 
1. Either the select statement has a group-by-clause, or the 
select-clause contains the "distinct" keyword. 
 
2. The from-clause has a common table expression or a nested 
table expression. 
 
3. The where-clause of the table expression has an equity 
predicate such as column1 = <constant>. 
 
4. The where-clause of the select statement has a predicate such 
as column1 = column2, in which column1 comes from the table 
expression, and column2 is part of a unique key of other tables 
referenced in the from-clause. And column2 is not included in 
the select-clause, but column1 is included. 
 
 
 
 
An example of such a query is given below. Assume that 
T4(D1, D2, D4) is a unique key on table T4. 
 
 
SELECT DISTINCT T4.D1, T4.D3, T4.D4, T3.C3, T3.C4 
FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY T2.B2, T2.B3) as C1, 
            T2.B1 as C2, T2.B2 as C3,T2.B3 as C4 
     FROM 
         (SELECT T1.A1 as B1, T1.A2 as B2, T1.A3 as B3 
          FROM T1 
          WHERE (201101 = T1.A2) 
         ) AS T2 
    ) AS T3, 
    T4 
WHERE (T3.C1 <= 30) AND (1 <= T3.C1) AND (T4.D4 = T3.C2) AND 
      (T3.C4 = T4.D1) AND (T3.C3 = T4.D2) 
 
You can find the optimized select statement in db2exfmt output. 
 
 
The corresponding call stack can look like this: 
 
in <sqlno_kc_construct_kc_key> <sqlno_key_card.C:3502> 
in <sqlno_kc_key_card> <sqlno_key_card.C:7310> 
in <sqlno_kc_all_key_card> <sqlno_key_card.C:991> 
in <sqlno_prop_mate> <sqlno_prop_misc.C:2324> 
in <MATE> <sqlno_crule_pop.C:398> 
in <sqlno_crule_simple_mate> <sqlno_crule.C:11373> 
in <sqlno_crule_mate_root> <sqlno_crule.C:11129> 
in <sqlno_crule_mate> <sqlno_crule_main.C:1823>

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.7 FixPack 5.                            * 
****************************************************************

Add an equity predicate to the select statement. 
 
Given the above example, you can add predicate T4.D2 = 201101 to 
the select statement, and the select statement becomes 
 
 
SELECT DISTINCT T4.D1, T4.D3, T4.D4, T3.C4, T3.C3 
FROM 
    (SELECT ROW_NUMBER() OVER (ORDER BY T2.B2, T2.B3) as C1, 
            T2.B1 as C2, T2.B2 as C3,T2.B3 as C4 
     FROM 
         (SELECT T1.A1 as B1, T1.A2 as B2, T1.A3 as B3 
          FROM T1 
          WHERE (201101 = T1.A2) 
         ) AS T2 
    ) AS T3, 
    T4 
WHERE (T3.C1 <= 30) AND (1 <= T3.C1) AND (T4.D4 = T3.C2) AND 
      (T3.C4 = T4.D1) AND (T3.C3 = T4.D2) and (T4.D2 = 201101)

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 9.7 FixPack 5.

keiner bekannt / siehe Local-Fix

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

23.03.2011
09.12.2011
09.12.2011

9.7.FP5