Why won't DB2 use my MQT table?

Question

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

Answer 1

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.