DB2 FOR LUW / DB2FORLUW / 950 - 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.5 FixPak 6                          * 
****************************************************************

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.5 Fix Pack 6a for Linux, UNIX, and Windows
 DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
 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
 

Problem was first fixed in Version 9.5 FixPak 6

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC66084 IC66085 IC67678 
follow-up : 

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

03.02.2010
24.06.2010
24.06.2010

9.5.