DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1.                       * 
****************************************************************

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

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
 

Problem was first fixed in DB2 Version 9.7 Fix Pack 1.

not known / see Local fix

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

12.08.2009
19.02.2010
19.02.2010

9.7.FP1