DB2 FOR LUW / DB2FORLUW / 970 - DB2

The Query compiler may try to use an existing MQT but can 
incorrectly fail to do so. 
The warning raised in the db2exfmt Extended Diagnostics output 
will be : 
 
Diagnostic Identifier:  2 
Diagnostic Details:     EXP0060W  The following MQT or 
statistical view was 
                        not eligible because one or more tables, 
views or 
                        subqueries specified in the MQT could 
not be found 
                        in the query: "DB2INST2"."DIM1_REP". 
 
Note that there may be genuine circumstances where this warning 
is raised. 
 
One example scenario where this problem would show is : 
 
create table dim1 ( col1 int not null, col2 int , col3 char(200) 
) in ts0; 
alter table dim1 add primary key ( col1 ) ; 
create table fact_1 ( col1 int, col2 int, col3 int, col4 int, 
col5 int ) DISTRIBUTE BY HASH("COL1") in ts1; 
create table fact_2 ( col1 int, col2 int, col3 int, col4 int, 
col5 int ) DISTRIBUTE BY HASH("COL1") in ts1; 
create table fact_3 ( col1 int, col2 int, col3 int, col4 int, 
col5 int ) DISTRIBUTE BY HASH("COL1") in ts1; 
create table fact_4 ( col1 int, col2 int, col3 int, col4 int, 
col5 int ) DISTRIBUTE BY HASH("COL1") in ts1; 
 
create view fact_all as ( 
  select * from fact_1 
  union all 
  select * from fact_2 
  union all 
  select * from fact_3 
  union all 
  select * from fact_4 ) 
; 
 
<populate tables with sufficient data> 
 
create table dim1_rep as  ( select col1 from dim1 ) 
data initially deferred refresh immediate in ts1 replicated; 
refresh table dim1_rep; 
 
insert into tab2 select count(*) 
from fact_all f, dim1 d1 
where d1.col1 = f.col1;

**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* MQT may not be used, leading to suboptimal query             * 
* performance.                                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 Fix Pack 5                               * 
****************************************************************

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

This problem has first fixed in V9.7 Fix Pack 5

not known / see Local fix

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

13.04.2011
17.12.2011
17.12.2011

9.7.FP5