DB2 - Problembeschreibung
Problem IC74783 | Status: Geschlossen |
DB2 CAN TRAP WHEN COMPILING A SPECIFIC TYPE OF SELECT STATEMENT | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 910 - 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 DB2 9.5 fix pack 8 or later. * **************************************************************** | |
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) | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 02.03.2011 09.12.2011 09.12.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5.FP8 | |
Problem behoben lt. FixList in der Version |