DB2 FOR LUW / DB2FORLUW / 970 - DB2

After detaching a partition from a range partitioned table with 
a dependent materialized query table (MQT), a unique global 
index might get created. 
If a unique global index gets created and the detached partition 
has any duplicates, refreshing the MQT might lead to incorrect 
results. 
 
The following commands illustrate how refreshing the MQT might 
lead to incorrect results: 
 
create table t1(rk int not null, uk int not null) partition by 
range(rk) (starting 0 ending 10001 every 1000); 
insert into t1 values(1,1),(2,1); 
 
create table t2 (uk int not null); 
insert into t2 values(1); 
 
create table m1 as (select t1.uk, count(*) as count from t1, t2 
where t1.uk=t2.uk group by t1.uk) data initially deferred 
refresh immediate; 
refresh table m1; 
 
select * from m1; 
 
UK          COUNT 
----------- ----------- 
          1           2 
 
  1 record(s) selected. 
 
alter table t1 detach partition part0 into part0; 
create unique index t1_uk on t1(uk) not partitioned; 
refresh table m1; 
 
select * from m1; 
 
UK          COUNT 
----------- ----------- 
          1           1 
 
  1 record(s) selected. 
 
To identify the MQTs that are affected by this problem, perform 
the following steps: 
 
1) Record the current query optimization level and set it to 3 
by issuing the following commands: 
 
select current query optimization from sysibm.sysdummy1; 
set current query optimization 3; 
 
2) Verify if querying the MQT and the MQT query definition 
return different result set by issuing the following commands: 
 
(mqt_query_definition) except all select * from mqt 
select * from mqt except all (mqt_query_definition) 
 
Using the previous example, the resultant queries are as 
follows: 
 
(select t1.uk, count(*) as count from t1, t2 where t1.uk=t2.uk 
group by t1.uk) except all select * from m1 
 
UK          COUNT 
----------- ----------- 
 
  0 record(s) selected. 
 
 
select * from m1 except all (select t1.uk, count(*) as count 
from t1, t2 where t1.uk=t2.uk group by t1.uk) 
 
UK          COUNT 
----------- ----------- 
          1           1 
 
  1 record(s) selected. 
 
If either of the EXCEPT ALL queries return any record, then that 
MQT is 
impacted and is returning incorrect result.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users running DB2 Version 9.7 GA through Fix Pack 6 for  * 
* Linux, Unix and Windows and creating global unique index     * 
* after detaching a partition.                                 * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Update to DB2 Version 9.7 Fix Pack 7 or follow Local Fix.    * 
****************************************************************

Run "REFRESH TABLE <mqt_name> NOT INCREMENTAL" to do a full 
refresh on the MQT

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 is first fixed in DB2 Version 9.7 Fix Pack 7.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC86029 IC86325 IC86330 
follow-up : 

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

09.07.2012
18.10.2012
07.12.2012

9.7.FP7