1.创建MQT
create table fif07c_mqt as
(SELECT d1.VAE527,d1.AAE043,
SUM(f0.VIF013) as VIF013,
SUM(f0.VIF014) as VIF014,
SUM(f0.VIF015) as VIF015,
SUM(f0.VIF033) as VIF033,
SUM(f0.VIF034) as VIF034,
SUM(f0.VIF035) as VIF035,
SUM(f0.VIF013 + f0.VIF014 + f0.VIF015) as vif036,
SUM(f0.VIF033 + f0.VIF034 + f0.VIF035) as vif037
FROM JHK.FIF07C f0,
JHK.DAAE043 d1,
JHK.DAAB301 d2,
JHK.DAAC004 d3,
JHK.DAAC012 d4,
JHK.DAIC161 d5,
JHK.DAAB020 d6,
JHK.DAAB022 d7,
JHK.DAAE043 d8,
JHK.DVAC564_2 d9,
JHK.DVAE561_4 d10,
JHK.DVAE571_1 d11
WHERE f0.AAE043 = d1.AAE043
AND f0.AAB301 = d2.AAB301
AND f0.AAC004 = d3.AAC004
AND f0.AAC012 = d4.AAC012
AND f0.AIC161 = d5.AIC161
AND f0.AAB020 = d6.AAB020
AND f0.AAB022 = d7.AAB022
AND f0.AAE043 = d8.AAE043
AND f0.VAC564 = d9.VAC564
AND f0.AIC048 = d10.VAE567
AND f0.VIF025 = d11.VAE575
AND (d1.VAE527=20074)
GROUP BY d1.VAE527,d1.AAE043
)
data initially deferred refresh DEFERRED;----(延迟刷新,此时表是暂挂的,刷新数据后即可用)
2.刷新数据
refresh table fif07c_mqt;----刷新数据
3.更新统计信息
runstats on table fif07c_mqt;
4.设置寄存器
设置db cfg dft_refresh_age to ANY-----只有当是refresh DEFERRED模式的时候才用设置,immediate的时候不用次设置,设置的目的是启用MQT
SET CURRENT REFRESH AGE ANY 仅对每次的连接会话起作用
5.查看是否使用MQT
运用工具db2exfmt
6.只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是,用户维护的 MQT 却允许对它们执行 insert、update 或 delete 操作。
7.EXP0054W The following REFRESH DEFERRED MQT was not considered for rewrite matching because the CURRENT REFRESH AGE register was not set to ANY: "JHK "."FIF07C_MQT". 和设置不是一个连接会话
8.对于immediate的刷新方式
只要SQL里面包含了sum,那么就一定要加count(sum里面的字段,几个sum这里就要有几个count),另外还要加一个count(1)
9. 在用get snapshot for dynamaic sql for dbname时,相同的SQL执行的时候,会增加执行的次数,时间也是根据次数累加的!!
10.MQT表的数据并不是一部分数据,而是SQL查出来的全部数据
11.MQT对前台的钻取是不支持的,仅仅支持一个层次而已
create table fif07c_mqt as
(SELECT d1.VAE527,d1.AAE043,
SUM(f0.VIF013) as VIF013,
SUM(f0.VIF014) as VIF014,
SUM(f0.VIF015) as VIF015,
SUM(f0.VIF033) as VIF033,
SUM(f0.VIF034) as VIF034,
SUM(f0.VIF035) as VIF035,
SUM(f0.VIF013 + f0.VIF014 + f0.VIF015) as vif036,
SUM(f0.VIF033 + f0.VIF034 + f0.VIF035) as vif037
FROM JHK.FIF07C f0,
JHK.DAAE043 d1,
JHK.DAAB301 d2,
JHK.DAAC004 d3,
JHK.DAAC012 d4,
JHK.DAIC161 d5,
JHK.DAAB020 d6,
JHK.DAAB022 d7,
JHK.DAAE043 d8,
JHK.DVAC564_2 d9,
JHK.DVAE561_4 d10,
JHK.DVAE571_1 d11
WHERE f0.AAE043 = d1.AAE043
AND f0.AAB301 = d2.AAB301
AND f0.AAC004 = d3.AAC004
AND f0.AAC012 = d4.AAC012
AND f0.AIC161 = d5.AIC161
AND f0.AAB020 = d6.AAB020
AND f0.AAB022 = d7.AAB022
AND f0.AAE043 = d8.AAE043
AND f0.VAC564 = d9.VAC564
AND f0.AIC048 = d10.VAE567
AND f0.VIF025 = d11.VAE575
AND (d1.VAE527=20074)
GROUP BY d1.VAE527,d1.AAE043
)
data initially deferred refresh DEFERRED;----(延迟刷新,此时表是暂挂的,刷新数据后即可用)
2.刷新数据
refresh table fif07c_mqt;----刷新数据
3.更新统计信息
runstats on table fif07c_mqt;
4.设置寄存器
设置db cfg dft_refresh_age to ANY-----只有当是refresh DEFERRED模式的时候才用设置,immediate的时候不用次设置,设置的目的是启用MQT
SET CURRENT REFRESH AGE ANY 仅对每次的连接会话起作用
5.查看是否使用MQT
运用工具db2exfmt
6.只有 REFRESH DEFERRED 物化查询表可以定义为 MAINTAINED BY USER。不能对用户维护的 MQT 发出 REFRESH TABLE 语句(用于系统维护的 MQT)。但是,用户维护的 MQT 却允许对它们执行 insert、update 或 delete 操作。
7.EXP0054W The following REFRESH DEFERRED MQT was not considered for rewrite matching because the CURRENT REFRESH AGE register was not set to ANY: "JHK "."FIF07C_MQT". 和设置不是一个连接会话
8.对于immediate的刷新方式
只要SQL里面包含了sum,那么就一定要加count(sum里面的字段,几个sum这里就要有几个count),另外还要加一个count(1)
9. 在用get snapshot for dynamaic sql for dbname时,相同的SQL执行的时候,会增加执行的次数,时间也是根据次数累加的!!
10.MQT表的数据并不是一部分数据,而是SQL查出来的全部数据
11.MQT对前台的钻取是不支持的,仅仅支持一个层次而已
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11795524/viewspace-450364/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11795524/viewspace-450364/