DB2 FOR LUW / DB2FORLUW / 970 - DB2

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

**************************************************************** 
* 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.7 FixPak 2                          * 
****************************************************************

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".

DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 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 Version 9.7 FixPak 2

not known / see Local fix

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

04.02.2010
25.05.2010
25.05.2010

9.7.