Why won't DB2 use my MQT table?

I've created a user-maintained MQT (Materialized Query Table) in DB2 9.7 (not yet fixpack 1). It's a simple grouping of the main fact table. But my queries aren't being rewritten to hit it.

Here's what I've tried:

• Created the MQT with ENABLE QUERY OPTIMIZATION and MAINTAINED BY USER characteristics
• Also included REFRESH DEFERRED & DATA INITIALLY DEFERRED. (Maybe开发者_如何学Python I shouldn't have?)
• Set registry variables telling DB2 to use all types of MQTs for optimization
• Ran "SET INTEGRITY for tableX ALL IMMEDIATE UNCHECKED"
• Ran runstats
• Flushed the cache: FLUSH PACKAGE CACHE DYNAMIC
• Ensured that default query optimization class was at least at level 2 (it's at 5)
• Set default refresh age to 0 (tho I assume this doesn't matter with user-defined MQTs)

Then tried to determine if the optimizer would use the MQT:

• Tried various simple queries that I expect to use the MQT - either:
  • SELECT COUNT(*) FROM fact_table
  • or SELECT group-dimension, COUNT(*) FROM fact_table GROUP BY group-dimension.
• Explain (using db2expln) only referenced the fact table and not the MQT
• Query results showed counts consistent with the fact table and not MQT table
• Query duration was consistent with fact table and not MQT table.

Any suggestions on either a simpler way to tell if a query is using an MQT or what I should try next to get it to use it?

2 things:

1) What is the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION register set to? It defaults to whatever the DFT_MTTB_TYPES database configuration parameter is -- and the default value is 'SYSTEM' - so the optimizer would ignore your MQT.

2) Also, your assumption about DFT_REFRESH_AGE and MAINTAINED BY USER MQTs is wrong. DFT_REFRESH_AGE still applies -- for a user-maintained MQT, the CURRENT REFRESH AGE register must be set to ANY in order for a refresh deferred MQT to be considered.

To debug the issue prepend the query with:

EXPLAIN PLAN FOR

and run it, then retrieve the diagnostic messages:

SELECT EXPLAIN_TIME, DIAGNOSTIC_ID, MSG
    FROM TABLE(EXPLAIN_GET_MSGS(
      CAST(NULL AS VARCHAR(128)),
      CAST(NULL AS TIMESTAMP),
      CAST(NULL AS VARCHAR(128)),
      CAST(NULL AS VARCHAR(128)),
      CAST(NULL AS VARCHAR(64)),
      CAST(NULL AS CHAR(1)),
      CAST(NULL AS INTEGER),
      CAST(NULL AS INTEGER),
      'en_US'))
    AS REGISTRYINFO
    WHERE EXPLAIN_TIME >= (CURRENT TIMESTAMP - 1 HOUR)
    ORDER BY EXPLAIN_TIME desc
;

In my case it was:

EXPLAIN_TIME              DIAGNOSTIC_ID MSG                                                                                                                                                                                                                
------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2015-07-08 10:48:50.940231 1             EXP0053W  The materialized query table "DB2INST1"."XFACETATTR" was not considered for query matching because the isolation level of the query is higher than the isolation level of the materialized query table.