DB2 FOR LUW / DB2FORLUW / 910 - 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 DB2 9.5 fix pack 8 or later.                      * 
****************************************************************

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)

not known / see Local fix

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

02.03.2011
09.12.2011
09.12.2011

9.5.FP8