DB2 FOR LUW / DB2FORLUW / 970 - DB2

DB2 compiler can automatically consider redirecting a portion of 
query's to MQT. When all the following conditions are satisfied, 
the rewritten query may return incorrect result. 
 
Notation: 
TS1, TS2: a single basetable/nickname or multiple 
basetables/nicknames inner joined together. 
colX, colY, colZ : a set of base table attributes or 
expressions. 
<columns> : arbitrary group of columns. 
<predicates> : arbitrary group of predicates. 
<aggregates> : arbitrary group of aggregations. 
 
Conditions: 
1. MQT query definition is either 
"SELECT TS1.colX, <aggregates> FROM TS1 where <predicates> GROUP 
BY TS1.colX". 
 
2. Query statement has a SELECT DISTINCT block whose output 
includes nothing from TS1, e.g. 
"SELECT DISTINCT TS2.colZ FROM TS1, TS2 where <predicates>" , 
 
or Query references TS1 >=2 times and Query has a SELECT 
DISTINCT block whose output includes some but not all MQT 
Group-By columns, e.g. 
"SELECT <columns> FROM (SELECT DISTINCT TS1.colY FROM TS1), TS2 
where <predicates>". 
 
Here, colX is a strict superset of colY. 
 
3. TS2.colX does not contain unique key of all TS2 tables. 
 
4. TS2.colZ contains unique key of all TS2 tables. 
 
Symptom: 
Query's select distinct or group-by column(s) has same values in 
query result set. In db2exfmt generated plan file, the 
diagnostic message shows MQT is used.

**************************************************************** 
* USERS AFFECTED:                                              * 
* All users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.7 FP8                                      * 
****************************************************************

Set "CURRENT QUERY OPTIMIZATION" special register to 0, 1 or 3, 
or drop the MQT table in question.

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
 

The problem was first fixed in DB2 v9.7 FP8

See the APAR description

forerunner  : APAR is sysrouted TO one or more of the following: IC90174 
follow-up : 

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

26.11.2012
08.04.2013
08.04.2013

9.7.FP8