Add Materialized Query Table into a Fact table in Olap Center

/*
　　使用Olap Center加入Fact Table時，MQT 是不會出現在選取的 Table 清單之中.
　　但是可以先將MQT alter 成 Regular Table 後加入Cube所使用的Fact　　
*/

/*　Create Testing MQT */

DROP TABLE ORION_MQT
CREATE TABLE ORION_MQT AS
(
　　SELECT * FROM T1
) DATA INITIALLY DEFERRED REFRESH DEFERRED in userspace1;

/* Refresh 資料進去 */

REFRESH TABLE ORION_MQT;

/* Alter 成 regular table */

ALTER TABLE ORION_MQT DROP MATERIALIZED QUERY


/*
　　開啟Olap Center
　　原本已建立一個名為 Orion 的 Cube，現在選取 ORION_MQT 成 FACT TABLE
*/


/* 再將ORION_MQT 還原成Materialized Query Table */

ALTER TABLE ORION_MQT ADD MATERIALIZED QUERY
(
　　SELECT * FROM T1
) DATA INITIALLY DEFERRED REFRESH DEFERRED

SET INTEGRITY FOR ORION_MQT ALL IMMEDIATE UNCHECKED


/*
　　恢復成MQT之後，則可以再進行Refresh。
　　※　 並不會影響使用 Cube 的 Tool。such as QMF 這類交叉分析的 tool。
　　　　只是再開啟 Olap Center 之後，ORION_MQT 被 mark 一個警告。
*/

Partitioned MQT (II) using DB2 9

/*
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.


Partitioned MQT (I) using DB2 9

/*
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.


Data movement on Materialized Query Table using DB2 UDB8.2

/*　建立測試資料　*/

CREATE TABLE DATAMOVE_TEST
(
　　X INTEGER,
　　Y INTEGER,
　　XY INTEGER
) IN USERSPACE1;

/*　新增測試資料一萬筆　*/

INSERT INTO DATAMOVE_TEST
WITH N(X) AS (
SELECT　1 X
FROM　　SYSIBM.SYSDUMMY1
UNION　　ALL
SELECT　N.X + 1
FROM　　N
WHERE　N.X + 1 <= 100
)
SELECT　N.X , N1.X , N.X * N1.X
FROM　　N,N N1;

/*　建立MQT放X>50的資料　*/

CREATE TABLE MQT_GREATERTHAN5000 AS
(
　　SELECT X,Y,XY
　　FROM DATAMOVE_TEST
　　WHERE X > 50
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1;

/*　將資料轉入MQT　*/

REFRESH TABLE MQT_GREATERTHAN5000;

/*　建立MQT放X<=50的資料　*/

CREATE TABLE MQT_NOGREATERTHAN5000 AS
(
　　SELECT X,Y,XY
　　FROM DATAMOVE_TEST
　　WHERE X <= 50
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1;

/*　將資料轉入MQT　*/

REFRESH TABLE MQT_NOGREATERTHAN5000;

/*　建一個MQT: MQT_TOTAL　*/

CREATE TABLE MQT_TOTAL AS
(
　　SELECT 0 X,0 Y,0 XY FROM SYSIBM.SYSDUMMY1
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1;

※若於此時對MQT_TOTAL做REFRESH，則LOAD完，SET INTEGRITY後，SELECT MQT_TOTAL會有10001筆資料

/*　於CLP下對MQT_GREATERTHAN5000及MQT_NOGREATERTHAN5000做EXPORT　*/

C:\>db2 export to 1.del of del select * from mqt_greaterthan5000
C:\>db2 export to 2.del of del select * from mqt_nogreaterthan5000

/*　將.del資料load進MQT_TOTAL　*/

C:\>db2 load from 1.del of del insert into mqt_total
C:\>db2 load from 2.del of del insert into mqt_total

/*　SELECT MQT_TOTAL　*/

C:\>db2 select * from mqt_total
X　　Y　　XY
---- ---- ----
SQL0668 由於原因碼"1"，不容許表格"ORION.MQT_TOTAL"上的作業。
SQLSTATE=57016

/*　SET INTEGRITY */

C:\>db2 set integrity for mqt_total all immediate unchecked
DB20000I　SQL　命令已順利完成。

C:\>db2 select count(*) from mqt_total

---------
10000


Materialized Query Table in DB2 UDB8.2

/* 建立測試table */

CREATE TABLE TEST
(
　　PRDID CHAR(1),
　　TRADEDT CHAR(8),
　　PRICE DECIMAL(19,2)
) IN USERSPACE1;

CREATE TABLE TEST1
(
PRDID CHAR(1) NOT NULL PRIMARY KEY,
PRDNAME VARCHAR(32)
) IN USERSPACE1;

CREATE TABLE TEST3
(
TRADEMON CHAR(6),
TOTAL DECIMAL(19,2)
) IN USERSPACE1;

/* 新增測試資料 */

INSERT INTO TEST VALUES ('1','20080301',33.62);
INSERT INTO TEST VALUES ('1','20080403',100.05);
INSERT INTO TEST VALUES ('2','20080205',50.93);
INSERT INTO TEST VALUES ('1','20080409',120.73);
INSERT INTO TEST VALUES ('2','20080301',99.12);
INSERT INTO TEST VALUES ('1','20080501',162.53);
INSERT INTO TEST VALUES ('1','20080530',195.99);
INSERT INTO TEST VALUES ('2','20080502',88.76);
INSERT INTO TEST VALUES ('2','20080530',90.23);

INSERT INTO TEST1 VALUES ('1','PRODUCT A');
INSERT INTO TEST1 VALUES ('2','PRODUCT B');

/* 建立Materialized Query Table(MQT) */

CREATE TABLE MQT_TEST AS
(
　SELECT TEST.PRDID,PRDNAME,AVG(PRICE) AVG_PRICE
　FROM TEST
　LEFT JOIN TEST1 ON TEST1.PRDID = TEST.PRDID
　GROUP BY TEST.PRDID,PRDNAME
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1;

/* 將資料轉入MQT */

REFRESH TABLE MQT_TEST;

/* View可建立在MQT之上做查詢，反之則不然 */

CREATE VIEW V_MQT_TEST
AS
SELECT * FROM MQT_TEST;

※這樣做並沒有什麼好處，因MQT重建時，View會被標記為無效(SQL0575N)。View 必須被重建

/* 再測試Stored procedure包含MQT的狀況 */
/* 建立一個MQT:MQT_TEST2 */

CREATE TABLE MQT_TEST2 AS (
SELECT TEST.PRDID,PRDNAME,TRADEDT,SUM(PRICE) TOTAL
FROM TEST
LEFT JOIN TEST1 ON TEST1.PRDID = TEST.PRDID
GROUP BY TEST.PRDID,PRDNAME,TRADEDT
) DATA INITIALLY DEFERRED REFRESH DEFERRED IN USERSPACE1;

REFRESH TABLE MQT_TEST2;

/*　ex: 建立procedure: sp_test 內容如下　*/

DROP PROCEDURE SP_TEST
GO
CREATE PROCEDURE SP_TEST
SPECIFIC SP_TEST
LANGUAGE SQL
BEGIN

　　　　DECLARE SQLCODE INTEGER DEFAULT 0;
　　　　DECLARE retcode INTEGER DEFAULT 0;
　　　　declare szsql VARCHAR(1024);
　　　　DECLARE nCOL1 INTEGER;

　　　　DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,
　　　　　　　　　　　　　　　　　　　　　SQLWARNING,
　　　　　　　　　　　　　　　　　　　　　NOT FOUND
　　　　SET RETCODE = SQLCODE;

　　　　SET szsql = 'ALTER TABLE TEST3 ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';
　　　　EXECUTE IMMEDIATE szsql;

　　　　INSERT　INTO TEST3
　　　　SELECT　SUBSTR(TRADEDT,1,6),SUM(TOTAL)
　　　　FROM　　MQT_TEST2
　　　　GROUP　　BY SUBSTR(TRADEDT,1,6);

　　　　RETURN RETCODE;
END

/* MQT_TEST2正常有資料下Call procedure */

C:\>db2 call sp_test
傳回狀態 = 0

C:\>db2 select * from TEST3

/* 結果如下 */

200802　50.93
200803　132.74
200804　220.78
200805　537.51

/* DROP MQT_TEST2 then Call procedure */

C:\>db2 call sp_test
傳回狀態 = -727

/* CREATE MQT_TEST2 未 refresh then Call procedure */

C:\>db2 call sp_test
傳回狀態 = -668

※ MQT正確被Refresh後，procedure 可正常被執行，不須重建