How to create a incremental replicated MQT

How to create a incremental replicated MQT :

There are 2 ways, If you are inserting into you table then follow steps 1 .if u have load then follow setp 2

STEP1:

CREATE TABLE SCHEMA.SAMPLE LIKE SCHEMA.SAMPLE ;

CREATE UNIQUE INDEX "SCHEMA"."XUIt_30" ON "SCHEMA"."SAMPLE"
                ("RUN_ID" ASC,
                 "TABLE_ID" ASC,
                 "BUSINESS_DATE" ASC,
                 "ROW_ID" ASC)
                CLUSTER ALLOW REVERSE SCANS;
-- DDL Statements for primary key on Table "SCHEMA"."SAMPLE"

ALTER TABLE "SCHEMA"."SAMPLE"
        ADD CONSTRAINT "XUIHUBt_30" PRIMARY KEY
                ("RUN_ID",
                 "TABLE_ID",
                 "BUSINESS_DATE",
                 "ROW_ID");
DELETE FROM SCHEMA.SAMPLE

INSERT INTO SCHEMA.SAMPLE SELECT * FROM SCHEMA.SAMPLE FETCH FIRST 1000 ROWS ONLY;

CREATE SUMMARY TABLE SCHEMA.R_SAMPLE AS (SELECT *
FROM SCHEMA.SAMPLE ) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE SCHEMA.R_SAMPLE ;

CREATE TABLE  SCHEMA.STG_SAMPLE FOR  SCHEMA.R_SAMPLE PROPAGATE IMMEDIATE;
SET INTEGRITY FOR SCHEMA.STG_SAMPLE IMMEDIATE CHECKED;

REFRESH TABLE SCHEMA.R_SAMPLE NOT INCREMENTAL;

SELECT COUNT(1) FROM SCHEMA.R_SAMPLE;

INSERT INTO SCHEMA.SAMPLE  SELECT a.* FROM SCHEMA.SAMPLE a WHERE (RUN_ID,BUSINESS_DATE,ROW_ID)
NOT IN (SELECT RUN_ID,BUSINESS_DATE,ROW_ID FROM SCHEMA.SAMPLE b );

SELECT COUNT(1) FROM SCHEMA.STG_SAMPLE;
SELECT COUNT(1) FROM SCHEMA.R_SAMPLE;

REFRESH TABLE SCHEMA.R_SAMPLE INCREMENTAL;

SELECT COUNT(1) FROM SCHEMA.R_SAMPLE;
SELECT COUNT(1) FROM SCHEMA.STG_SAMPLE;





STEP 2:

db2 "export to SCHEMA.SAMPLE of del select * FROM SCHEMA.SAMPLE"

CREATE TABLE SCHEMA.SAMPLE LIKE SCHEMA.HUB_TAPS_CASH ;

CREATE UNIQUE INDEX "SCHEMA"."XUIHUBt_30" ON "SCHEMA"."SAMPLE"
                ("RUN_ID" ASC,
                 "TABLE_ID" ASC,
                 "BUSINESS_DATE" ASC,
                 "ROW_ID" ASC)
                CLUSTER ALLOW REVERSE SCANS;
-- DDL Statements for primary key on Table "SCHEMA"."SAMPLE"

ALTER TABLE "SCHEMA"."SAMPLE"
        ADD CONSTRAINT "XUIHUBt_30" PRIMARY KEY
                ("RUN_ID",
                 "TABLE_ID",
                 "BUSINESS_DATE",
                 "ROW_ID");

select count(1) from SCHEMA.SAMPLE;

db2 " load client from /user/SCHEMA.SAMPLE of del  rowcount 1000 INSERT INTO SCHEMA.SAMPLE "

CREATE SUMMARY TABLE SCHEMA.R_SAMPLE AS (SELECT *
FROM SCHEMA.SAMPLE ) DATA INITIALLY DEFERRED REFRESH DEFERRED;

REFRESH TABLE SCHEMA.R_SAMPLE ;

CREATE TABLE  SCHEMA.STG_SAMPLE FOR  SCHEMA.R_SAMPLE PROPAGATE IMMEDIATE;
SET INTEGRITY FOR SCHEMA.STG_SAMPLE IMMEDIATE CHECKED;

REFRESH TABLE SCHEMA.R_SAMPLE NOT INCREMENTAL;

SELECT COUNT(1) FROM SCHEMA.R_SAMPLE;

db2 " load client from /user/SCHEMA.HUB_TAPS_CASH of del   INSERT INTO SCHEMA.SAMPLE "
SET INTEGRITY FOR SCHEMA.SAMPLE IMMEDIATE CHECKED;


----
SET INTEGRITY FOR SCHEMA.STG_SAMPLE IMMEDIATE CHECKED;
REFRESH TABLE SCHEMA.R_SAMPLE INCREMENTAL;

SELECT COUNT(1) FROM SCHEMA.R_SAMPLE;
SELECT COUNT(1) FROM SCHEMA.STG_SAMPLE;