Materialized Query Tables and the DB2 Optimizer

Hello folks,

I've got a problem with materialized query tables (mqt).
The problem is that somehow the DB2 optimizer doesn't use the mqt for optimizing a query.
I've created a mqt like this:

db2 CREATE TABLE mqt_table(a,b) AS (SELECT a_a, b_b from a, b where a.some = b.some) DATA INITIALLY DEFERRED REFRESH DEFERRED ENABLE QUERY OPTIMIZATION IN USERSPACE1

now that statement goes through without difficulties.
Then I did:

db2 REFRESH TABLE mqt_table

again no problems.

now if I execute the select statement from the mqt_table shouldn't the optimizer just take the already materialized table and return the results instead of joining a and b again?

I did db2expln on the query above and it did just that (the access plan was without the mqt, just plain joins and tbscans). The optimizer didn't use the mqt at all. I wanted to know if the optimizer uses the mqt at all, since I have other queries I want to issue and run with the help of a mqt (that didn't work either).

Do I need to create an index on one or more columns such that the optimizer recognizes this table or is there some Database cfg option I didn't turn on (well, I didn't find any)?

Thanks a lot!


Have fun,
Kitgo

----------------------------------
stupidity creates leisure time