I have produced a person-maintained MQT (Materialized Query Table) in DB2 9.7 (not fixpack 1). It is a simple grouping from the primary fact table. But my queries aren't being rewritten hitting it.

This is what I have attempted:

  • Produced the MQT with ENABLE QUERY Optimisation and MAINTAINED BY USER qualities
  • Also incorporated REFRESH DEFERRED &lifier DATA INITIALLY DEFERRED. (Maybe I should not have?)
  • Set registry variables telling DB2 to make use of all kinds of MQTs for optimisation
  • Went runstats
  • Flushed the cache: FLUSH PACKAGE CACHE DYNAMIC
  • Made certain that default query optimisation class was a minimum of at level 2 (it's at 5)
  • Set default refresh age to (tho I suppose this does not matter with user-defined MQTs)

Then attempted to find out when the optimizer would make use of the MQT:

  • Attempted various simple queries which i be prepared to make use of the MQT - either:
    • Choose COUNT(*) FROM fact_table
    • or Choose group-dimension, COUNT(*) FROM fact_table GROUP BY group-dimension.
  • Explain (using db2expln) only recommended the very fact table and never the MQT
  • Query results demonstrated counts in conjuction with the fact table and never MQT table
  • Query duration was in line with fact table and never MQT table.

Any suggestions on whether simpler method to know if a question is applying an MQT or things i need next to have it for doing things?

2 things:

1) What's the CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION register set to? It defaults to regardless of the DFT_MTTB_TYPES database configuration parameter is -- and also the default value is 'SYSTEM' - therefore 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 is applicable -- for any user-maintained MQT, the CURRENT REFRESH AGE register should be set to the for a refresh deferred MQT that need considering.