DB2 FOR LUW / DB2FORLUW / 980 - 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 and         * 
* referring to MQT with group by clause can cause an instance  * 
* crash.                                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to db2 Version 9.8 FixPak 3                          * 
****************************************************************

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.8 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.8 Fix Pack 4 for AIX and Linux
 DB2 Version 9.8 Fix Pack 5 for AIX and Linux
 

Problem was first fixed in Version 9.8 FixPak 3

not known / see Local fix

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

06.04.2010
29.12.2010
29.12.2010

9.8.