title: Why won't DB2 use my MQT table?
body: <p>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.</p>
  
  <p>Here's what I've tried:</p>
  
  <ul>
  <li>Created the MQT with ENABLE QUERY OPTIMIZATION and MAINTAINED BY USER characteristics</li>
  <li>Also included REFRESH DEFERRED &amp; DATA INITIALLY DEFERRED. (Maybe I shouldn't have?)</li>
  <li>Set registry variables telling DB2 to use all types of MQTs for optimization</li>
  <li>Ran "SET INTEGRITY for tableX ALL IMMEDIATE UNCHECKED"</li>
  <li>Ran runstats</li>
  <li>Flushed the cache: FLUSH PACKAGE CACHE DYNAMIC</li>
  <li>Ensured that default query optimization class was at least at level 2 (it's at 5)</li>
  <li>Set default refresh age to 0 (tho I assume this doesn't matter with user-defined MQTs)</li>
  </ul>
  
  <p>Then tried to determine if the optimizer would use the MQT:</p>
  
  <ul>
  <li>Tried various simple queries that I expect to use the MQT - either:
  
  <ul>
  <li>SELECT COUNT(*) FROM fact_table</li>
  <li>or SELECT group-dimension, COUNT(*) FROM fact_table GROUP BY group-dimension.</li>
  </ul></li>
  <li>Explain (using db2expln) only referenced the fact table and not the MQT</li>
  <li>Query results showed counts consistent with the fact table and not MQT table</li>
  <li>Query duration was consistent with fact table and not MQT table.</li>
  </ul>
  
  <p>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?</p>