/*
My scenario: 利用已經做好的 MQT MQT_DAY_LIST，
假設現行資料檔 DAY_LIST 有異動
MQT_DAY_LIST 應該如何去做 REFRESH
*/
/* 刪除 DAY_LIST 所有大於 2008-09-11 的日期 */
delete from day_list where dt > '2008-09-11'
DB20000I The SQL command completed successfully.
/* MQT_DAY_LIST 還未受影響 */
select count(*) from mqt_day_list
1
-----------
1096
1 record(s) selected.
/* MQT_DAY_LIST 的 PARTITIONS 如下 */
PARTITIONNAME LOWVALUE HIGHVALUE
----------------- --------------- -----------------
PART0 '2008-01-01' '2008-12-31'
PART2007 '2007-01-01' '2007-12-31'
PART2006 '2006-01-01' '2006-12-31'
3 record(s) selected.
/* 將 PARTITION PART2007 DETACH 掉 */
alter table mqt_day_list detach partition part2007 into mqt_part2007
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
/* 檢查完整性同時做REFRESH */
set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.
/* 將 PARTITION PART2006 DETACH 掉 */
alter table mqt_day_list detach partition part2006 into mqt_part2006
SQL3601W The statement caused one or more tables to automatically be placed
in the Set Integrity Pending state. SQLSTATE=01586
/* 檢查完整性同時做REFRESH */
set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.
/* MQT_DAY_LIST 還有多少筆數 */
select count(*) from mqt_day_list
1
-----------
255
1 record(s) selected.
/* 把 MQT_DAY_LIST 變更為一般的 Table */
alter table mqt_day_list drop materialized query
DB20000I The SQL command completed successfully.
/* 把才 DETACH 的PARTITION 分別再 ATTACH 回來 */
alter table mqt_day_list attach partition part2007 starting '2007-01-01' ending '2007-12-31' from mqt_part2007
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586
alter table mqt_day_list attach partition part2006 starting '2006-01-01' ending '2006-12-31' from mqt_part2006
DB20000I The SQL command completed successfully.
/* 檢查完整性同時做REFRESH */
set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.
/* 新的DAY_LIST資料進來+2007年度+2006年度資料 */
select count(*) from mqt_day_list
1
-----------
985
1 record(s) selected.
/* 把 MQT_DAY_LIST 變更為MQT Table */
alter table mqt_day_list add materialized query
(select dt from day_list)
data initially deferred refresh deferred
DB20000I The SQL command completed successfully.
/* 由於還未經過 SET INTEGRITY，因此沒辦法做 SELECT */
select count(*) from mqt_day_list
1
-----------
SQL0668N Operation not allowed for reason code "1" on table
"DB2ADMIN.MQT_DAY_LIST". SQLSTATE=57016
/*
這裡一樣不能做checked，否則 MQT 重新 REFRESH 後，就只剩 DAY_LIST 的資料
這也是為什麼需要將 MQT 變更為一般的 TABLE 再來做 ATTACH
*/
set integrity for mqt_day_list all immediate unchecked
DB20000I The SQL command completed successfully.
/* 接著就可以檢查筆數是否正確 */
select count(*) from mqt_day_list
1
-----------
985
1 record(s) selected.
