Hi,
I've read the former postings but was not able to solve my problem:
I have a Summary Table (or MQT as you like it) and the query optimizer
does not seem to use the summary table.
I run the following commands:
set current query optimization 9;
set current refresh age = any;
drop table db2admin.PIW_MQ T_PROC_AVGDUR;
create summary table db2admin.PIW_MQ T_PROC_AVGDUR AS (select
p.proc_template _name, d.the_year, d.quarter, d.month_of_year ,
count(*) as count,
avg(p.overall_d uration) as avg_duration,
max(p.overall_d uration) as max_duration,
min(p.overall_d uration) as min_duration,
avg(p.waiting_d uration) as wait_duration,
max(p.waiting_d uration) as max_waitduratio n,
min(p.waiting_d uration) as min_waitduratio n
from db2admin.piw_pr ocess p, db2admin.piw_da te d
where p.start_date_ke y=d.date_key
group by p.proc_template _name, d.the_year, d.quarter, d.month_of_year )
Data initially deferred
Refresh deferred
Enable Query Optimization
Maintained by system
Not Logged initially
IN EVENTSPACE ;
comment on table db2admin.PIW_MQ T_PROC_AVGDUR Is 'AST created for
OLAPEVT';
commit;
Alter Table db2admin.PIW_MQ T_PROC_AVGDUR Activate Not Logged Initially;
Refresh Table db2admin.PIW_MQ T_PROC_AVGDUR;
commit;
Runstats On Table db2admin.PIW_MQ T_PROC_AVGDUR;
commit;
(I took the sequence of commands from the example sql script to create
a summary table:
http://publib.boulder.ibm.com/infoce...coptscript.htm)
Afterwards I try the following command in the Command Editor:
select p.proc_template _name, d.the_year, d.quarter, d.month_of_year ,
count(*) as count,
avg(p.overall_d uration) as avg_duration,
from db2admin.piw_pr ocess p, db2admin.piw_da te d
where p.start_date_ke y=d.date_key
group by p.proc_template _name, d.the_year, d.quarter, d.month_of_year
Viewing the Access Plan (Explain SQL) the query is using the basic
tables (piw_date, piw_process) and not the summary table.
Any ideas? Are there any other settings I didn't consider?
Thanks,
Andrea