DB2 - Problembeschreibung
Problem IC66056 | Status: Geschlossen |
A QUERY CONTAINING MAX OR MIN AGGREGATE FUNCTION AND REFERRING TO MQT WITH GROUP BY CLAUSE CAUSES AN INSTANCE CRASH | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 | |
Problembeschreibung: | |
A query containing MAX or MIN aggregate function and referring to MQT with group by clause can cause an instance crash. For example, -- MQT defintion create table t1 (x int); create table t2 (a int, b int); create table MQT as ( select x from t1 group by x ) data initially deferred refresh deferred; refresh table mqt; -- Query traps select min(b) from t1, (select a, b from t2 group by a, b) where x = a and a = 1 group by a; To hit the trap, the following conditions need to be satisfied: 1. MQT has group by clause and it references to T1 but not T2. 2. Query has group by clause too. 3. In query, T1 joins with a (e.g. Group-By) subquery of T2. 4. Query contains MAX or MIN aggregate whose operand(s) involves column from T2 (e.g. min(b)). 5. All MQT Group-By columns are bound to constant in the query (i.e. x is bound to 1 due to query predicates "x=a and a = 1"). Trap stack includes get_comp_map sqlnr_trans_pid sqlnr_compensate_pid | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All * **************************************************************** * PROBLEM DESCRIPTION: * * A query containing MAX or MIN aggregate function * * andreferring to MQT with group by clause can cause an * * instancecrash. * **************************************************************** * RECOMMENDATION: * * Upgrade to db2 Version 9.5 FixPak 6 * **************************************************************** | |
Local-Fix: | |
1. "db2 drop table MQT" to remove MQT 2. "db2 set current refresh age 0" to disable refresh deferred MQT routing 3. Change query to "select min(b) from t1, t2 where x = a and a = 1". | |
verfügbare FixPacks: | |
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in Version 9.5 FixPak 6 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC66084 IC66085 IC67678 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 03.02.2010 24.06.2010 24.06.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.5. | |
Problem behoben lt. FixList in der Version |