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 IC62517 Status: Geschlossen

QUERY COMPILATION MAY BLOCK DUE TO OFF-LINE OPERATION ON A
MATERIALIZED QUERY (SUMMARY) TABLE (MQT) REFERENCED BY QUERY.

Produkt:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problembeschreibung:
A query being compiled may block waiting for an off-line 
operation to complete on a materialized query table (MQT). If 
this off-line operation, e.g., LOAD, is time consuming, query 
compilation time will be adversely affected. 
 
For such a scenario to occur, the MQT must referred to in the 
query directly or indirectly. Indirect reference will occur if 
the MQT is dependent on a base table used in the query. The 
off-line operation in question must acquire a blocking 
(exclusive) lock on the MQT and put it in CHECK PENDING state. 
 
An example scenario: 
 
Agent 1: 
-------- 
CONNECT TO DB; 
CREATE TABLE T1 (C1 INT); 
CREATE TABLE GBMQT AS (SELECT C1 FROM T1 GROUP BY C1) DATA 
INITIALLY DEFERRED REFRESH DEFERRED; 
-- T1 now has a dependent MQT named GBMQT 
REFRESH TABLE GBMQT; 
-- time-consuming LOAD operation will acquire Z-lock on MQT and 
put it in CHECK PENDING state 
LOAD FROM FILE.CSV OF DEL REPLACE INTO GBMQT; 
 
Agent 2: 
-------- 
CONNECT TO DB; 
SET CURRENT REFRESH AGE ANY; 
-- Query can be matched to GBMQT and will also indirectly refer 
to GBMQT 
-- Query compilation will be blocked waiting for LOAD operation 
to complete 
SELECT C1 FROM T1 GROUP BY C1; 
 
This APAR will address the impact on query compilation by 
ignoring such an MQT during query compilation. If EXPLAIN is 
enabled, a diagnostic message (EXP0058W) will be logged 
indicating that the MQT was not considered during query 
compilation due to it being in CHECK PENDING state.
Problem-Zusammenfassung:
**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1.                       * 
****************************************************************
Local-Fix:
- Alter the CURRENT QUERY OPTIMIZATION special register to a 
value of 0, 1 or 3. 
- Temporarily alter the MQT by disabling it for query 
optimization before initiating the off-line operation, e.g., for 
the above MQT, this can be done like so: 
ALTER TABLE GBMQT DROP MATERIALIZED QUERY; 
LOAD FROM FILE.CSV OF DEL REPLACE INTO GBMQT; 
ALTER TABLE GBMQT ADD MATERIALIZED QUERY (SELECT C1 FROM T1 
GROUP BY C1) DATA INITIALLY DEFERRED REFRESH DEFERRED; 
- Restrict MQTs that may be considered for optimization of 
queries by using the MQTOPT and MQT Optimizer Guideline 
elements.
verfügbare FixPacks:
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows

Lösung
Problem was first fixed in DB2 Version 9.7 Fix Pack 1.
Workaround
keiner bekannt / siehe Local-Fix
Weitere Daten
Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:
12.08.2009
19.02.2010
19.02.2010
Problem behoben ab folgender Versionen (IBM BugInfos)
9.7.FP1
Problem behoben lt. FixList in der Version
9.7.0.1 FixList