/*
My scenario : 先完成一個有歷史資料的 Materialized Query Table，
但 MQT create 在只有現行資料的 Table 上，
歷史資料用 attach 方式進去。
1) 前置處理(create table & insert data):
day_list存2008資料,day_2007存2007資料,day_2006存2006資料。
create partitioned mqt table
mqt建立在day_list上。
how to attach
使mqt_day_list有2008資料，也有歷史記錄2007及2006。
*/
/* 1) initiation - create table */
/* 使用之前用的day_list */
create table day_list
(
dt date
) partition by range(dt)
(starting '2008-01-01' ending '2008-12-31' every 3 months)
/* 新增2008日期資料 */
INSERT INTO DAY_LIST
SELECT DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01') + N DAY
FROM (SELECT (ROW_NUMBER() OVER() - 1) N
FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE YEAR(DATE(RTRIM(CHAR(YEAR(CURRENT DATE)))||'-01-01')+N DAY) = YEAR(CURRENT DATE);
/* 建立 day_2007 */
create table day_2007
(
dt date
) in userspace1;
/* 新增2007日期資料 */
INSERT INTO DAY_2007
SELECT DATE(RTRIM(CHAR(2007))||'-01-01') + N DAY
FROM (SELECT (ROW_NUMBER() OVER() - 1) N
FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE YEAR(DATE(RTRIM(CHAR(2007))||'-01-01')+N DAY) = 2007;
/* 建立 day_2006 */
create table day_2006
(
dt date
) in userspace1;
/* 新增2006日期資料 */
INSERT INTO DAY_2006
SELECT DATE(RTRIM(CHAR(2006))||'-01-01') + N DAY
FROM (SELECT (ROW_NUMBER() OVER() - 1) N
FROM SYSIBM.SYSTABLES FETCH FIRST 366 ROW ONLY) D
WHERE YEAR(DATE(RTRIM(CHAR(2006))||'-01-01')+N DAY) = 2006;
/* CREATE MQT TABLE */
create table mqt_day_list as
(
select dt
from day_list
)DATA INITIALLY DEFERRED REFRESH deferred
PARTITION BY RANGE(dt)
(STARTING '2008-01-01' ENDING '2008-12-31')
in userspace1;
/* 將資料滾進 MQT */
set integrity for mqt_day_list immediate checked
/* 或者使用REFRESH TABLE都可以 */
refresh table mqt_day_list
/* 檢查一下mqt的partition */
select datapartitionname,datapartitionid,lowvalue,highvalue
from sysibm.sysdatapartitions
where tabname = 'MQT_DAY_LIST'
PARTITIONNAME LOWVALUE HIGHVALUE
--------------------- ------------------ ----------------
PART0 '2008-01-01' '2008-12-31'
1 record(s) selected.
/* 確定資料滾進來 */
select count(*) from mqt_day_list
1
-----------
366
1 record(s) selected.
/* 讓 mqt 有 2007 及 2006 歷史資料 */
/* 先使 MQT Table 變成普通的 table */
alter table mqt_day_list drop materialized query
DB20000I The SQL command completed successfully.
/* 將先前做好的day_2007 attach上來 */
alter table mqt_day_list attach partition part2007 starting '2007-01-01' ending '2007-12-31' from day_2007
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586
/* 檢查完整性 */
set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.
/* 將先前做好的day_2006 也attach上來 */
alter table mqt_day_list attach partition part2006 starting '2006-01-01' ending '2006-12-31' from day_2006
SQL3601W The statement caused one or more tables to automatically be placed in the Set Integrity Pending state. SQLSTATE=01586
/* 檢查完整性 */
set integrity for mqt_day_list immediate checked
DB20000I The SQL command completed successfully.
/* 把變成普通 TABLE 的 MQT 再 ALTER 回 MQT Table */
alter table mqt_day_list add materialized query
(select dt from day_list) data initially deferred refresh deferred
/*
這時一定要unchecked
因為一旦checked，mqt 又會從day_list refresh資料進來
*/
C:\>db2 set integrity for mqt_day_list all immediate unchecked
DB20000I The SQL command completed successfully.
/* 檢查mqt筆數 */
C:\>db2 select count(*) from mqt_day_list
1
-----------
1096
1 record(s) selected.
