DB2 FOR LUW / DB2FORLUW / 950 - 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:                                         * 
* Performance may be suboptimal when MQTs are not matched      * 
* under the conditions outlined in the long description of     * 
* this apar.                                                   * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 and Fix Pack 8                    * 
****************************************************************

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 DB2 Version 9.5 and Fix Pack 8

not known / see Local fix

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

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

04.04.2011
29.07.2011
29.07.2011

9.5.