DB2 FOR LUW / DB2FORLUW / A10 - 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                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade the database server to V10.1 Fix Pack 3              * 
****************************************************************

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

DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

This problem was first fixed in DB2 V10.1 Fix Pack 3

not known / see Local fix

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

13.02.2013
21.10.2013
21.10.2013