home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Neueste VersionenFixList
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Haben Sie Probleme? - Kontaktieren Sie uns.
Kostenlos registrieren anmeldung-x26
Kontaktformular kontakt-x26

DB2 - Problembeschreibung

Problem IC75779 Status: Geschlossen

QUERY COMPILER MAY NOT MATCH MQT TO MODIFYING STMT WHOSE SOURCE SUBQUERY
HAS UNION ALL JOINING WITH MQT DEPENDENT BASE TABLE

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
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;
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* all                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* MQT may not be used, leading to suboptimal query             * 
* performance.                                                 * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V9.7 Fix Pack 5                               * 
****************************************************************
Local-Fix:
verfügbare FixPacks:
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

Lösung
This problem has first fixed in V9.7 Fix Pack 5
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
13.04.2011
17.12.2011
17.12.2011
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP5
Problem behoben lt. FixList in der Version
9.7.0.5 FixList