Bharath's Tech Blog

Row Change TIMESTAMP

DBAs are sometimes asked by the Application teams to help them in finding out the row/s updated Timestamp in a DB2 Table.

Row/s updated Timestamp can be found in multiple ways.

1.    UPDATE Trigger

More details about Triggers can be found in http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_createtrigger.html

2.    Temporal Tables

More details about the Temporal Tables can be found in

http://www.ibm.com/developerworks/data/library/techarticle/dm-1410temporal-tables-db2zos/index.html

3.    ROW CHANGE expression

More details about the ROW CHANGE expression can be found in

http://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_rowchangeexpression.html

I found ROW CHANGE expression easy to implement, compared to other approaches. Below mentioned testing has been done to find out row updated Timestamp using ROW CHANGE expression.

Step 1:

CREATE TABLE LPARFO.TEST1

(SNO                  INTEGER   NOT NULL WITH DEFAULT

,ID                      CHAR(10)  NOT NULL WITH DEFAULT

,ROWCHANGE TIMESTAMP NOT NULL GENERATED FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP)

IN DATABASE LPARFO;

Step 2:

INSERT INTO LPARFO.TEST1(SNO,ID) VALUES(1,'RFO7936');

INSERT INTO LPARFO.TEST1(SNO,ID) VALUES(2,'rfo7936');

Step 3:

UPDATE LPARFO.TEST1 SET SNO=3 WHERE SNO=1;

Step 4: 

SELECT ROW CHANGE TIMESTAMP FOR LPARFO.TEST1 FROM LPARFO.TEST1;