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 | |
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 |