---EP
drop table staff
create table staff ( id integer not null, name varchar(50) not null, deptno varchar(50) not null,RCT TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP  )
create unique index PK_STAFF ON STAFF(ID)
ALTER TABLE STAFF ADD CONSTRAINT PK_STAFF PRIMARY KEY(ID)
insert into staff values(1,'Killy',1)
insert into staff values(2,'Emily',2)
insert into staff values(3,'En',3)

drop table dept
create table dept ( deptno varchar(50) not null, desc varchar(100),RCT TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP  )
create unique index PK_DEPT ON DEPT(DEPTNO)
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO)
insert into dept values(1,'Finance')
insert into dept values(2,'Manager')
insert into dept values(3,'Sales')

--WW summary table creation and control table creation
drop table STAFF_DEPT
CREATE TABLE STAFF_DEPT ( id integer not null, name varchar(50) not null, deptno varchar(50) not null, desc varchar(100) )
drop table TLASTUPDTIME
create table TLASTUPDTIME(tabname VARCHAR(50), LASTUPDTIME TIMESTAMP)
INSERT INTO TLASTUPDTIME VALUES('STAFF',TIMESTAMP('1900-12-10-21.12.50.384000'))
INSERT INTO TLASTUPDTIME VALUES('DEPT',TIMESTAMP('1900-12-10-21.12.50.384000'))

--WW initial data populate into summary table
###########################Initial State#####################################
db2 => SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1

1
--------------------------
2015-12-10-22.27.19.855000

  1 record(s) selected.
INSERT INTO STAFF_DEPT SELECT s.id,s.name,d.deptno,d.desc from staff s, dept d where s.deptno=d.deptno
UPDATE TLASTUPDTIME SET LASTUPDTIME=TIMESTAMP('2015-12-10-22.27.19.855000') WHERE TABNAME='STAFF'
UPDATE TLASTUPDTIME SET LASTUPDTIME=TIMESTAMP('2015-12-10-22.27.19.855000') WHERE TABNAME='DEPT'

--EP data update
#########################Update staff table###############################################
insert into staff values(4,'KE',3)
##########################################################################################


--WW summary table incremental data update
db2 => SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1

1
--------------------------
2015-12-10-22.28.48.431000

  1 record(s) selected.

delete from staff_dept where id in (SELECT id FROM STAFF WHERE RCT >= (SELECT LASTUPDTIME FROM TLASTUPDTIME WHERE TABNAME='STAFF'))
INSERT INTO STAFF_DEPT SELECT s.id,s.name,d.deptno,d.desc from (SELECT * FROM STAFF WHERE RCT >= (SELECT LASTUPDTIME FROM TLASTUPDTIME WHERE TABNAME='STAFF')) s, dept d where s.deptno=d.deptno
UPDATE TLASTUPDTIME SET LASTUPDTIME=TIMESTAMP('2015-12-10-22.28.48.431000') WHERE TABNAME='STAFF'

--EP data update
#######################Update dept table##################################################
insert into dept values(4,'Dev')
############################################################################################


---WW summary table incremental data update
db2 => SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1

1
--------------------------
2015-12-10-22.30.28.481000

  1 record(s) selected.
delete from staff_dept where deptno in (SELECT deptno FROM dept WHERE RCT >= (SELECT LASTUPDTIME FROM TLASTUPDTIME WHERE TABNAME='DEPT'))
INSERT INTO STAFF_DEPT SELECT s.id,s.name,d.deptno,d.desc from staff s, (SELECT * FROM dept WHERE RCT >= (SELECT LASTUPDTIME FROM TLASTUPDTIME WHERE TABNAME='DEPT')) d where s.deptno=d.deptno
UPDATE TLASTUPDTIME SET LASTUPDTIME=TIMESTAMP('2015-12-10-22.30.28.481000') WHERE TABNAME='DEPT'