Hi All,

i have problem in using MQTs to increase the response time of my normal queries.

My original query is -

select sum(sale_amt) sale_amt from db2dba.daily_trx_sale_cmsn_fact a, db2dba.tm_lkup b where a.PAID_DT_KEY_ID = b.time_key_id;

Since we thought that MQTs will serve the purpose, we created MQTs.
Even after completing the following steps, still we find that the base table is being accessed and not the MQT.

a) created matview using the following command :

create table db2dba.mat_view1 as
(select year(b.time_date) as year,
month(b.time_date) as month, sum(a.SALE_AMT) as sale_amt
from db2dba.daily_trx_sale_cmsn_fact a, db2dba.tm_lkup b
where a.PAID_DT_KEY_ID = b.time_key_id
group by year(b.time_date), month(b.time_date)) data initially deferred refresh deferred
maintained by system enable query optimization ;

b) refreshed the mat view and set the following parameters:

refresh table db2dba.mat_view1
set integrity for db2dba.mat_view1 immediate checked
SET CURRENT REFRESH AGE = ANY
SET CURRENT QUERY OPTIMIZATION 7

We performed all these in "Command Editor" . Then , we executed the query stated first. The access plan in command editor shows that MQT is not being used.

One intersting fact is that - before MQT was created and refresh was done , the query was taking around 13 seconds. But after MQT creation, refresh and parameters were set , the query comes in 1 or 2 seconds . Not sure why this is not shown in the access plan.

I am using DB2/UDB - 8.1.10 version

Can anyone help in this regards??

Thanks,
Arunvijay

Did you update your table and MQT statistics?

Also, your MQT uses GROUP BY. Depending on the statistics, the aggregation of values across groupings may not be considered cheaper than directly querying the table.

Ya.. i did executed the runstat commands before running the query

RUNSTATS ON TABLE DB2dba.daily_trx_sale_cmsn_fact WITH DISTRIBUTION and detailed indexes all;

RUNSTATS ON TABLE db2dba.mat_view1;

But even then it was not using the MQTs.

How big are your tables? What's the access plan when you query the MQT directly? And are you using the same or a lower isolation level in your query - compared to the isolation level when the MQT was created?