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 可正常被執行，不須重建
