home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 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
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC74505 Status: Geschlossen

DB2 CAN TRAP WHEN COMPILING A SPECIFIC TYPE OF SELECT STATEMENT

Produkt:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problembeschreibung:
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>
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error description field for more information.            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to Version 9.5 FixPack 8.                            * 
****************************************************************
Local-Fix:
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)
verfügbare FixPacks:
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

Lösung
Problem was first fixed in DB2 UDB Version 9.5 FixPack 8.
Workaround
keiner bekannt / siehe Local-Fix
Bug-Verfolgung
Vorgänger  : APAR is sysrouted TO one or more of the following: IC74783 IC75200 
Nachfolger : 
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
16.02.2011
11.07.2011
11.07.2011
Problem behoben ab folgender Versionen (IBM BugInfos)
9.5.FP8
Problem behoben lt. FixList in der Version
9.5.0.8 FixList