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
