Determining when a row was changed

If a table has a ROW CHANGE TIMESTAMP column, you can determine when a row was changed.

Procedure

To determine when a row was changed:

Issue a SELECT statement with the ROW CHANGE TIMESTAMP column in the column list.

If a qualifying row does not have a value for the ROW CHANGE TIMESTAMP column, Db2 returns the time that the page in which that row resides was updated.

Example

Suppose that you issue the following statements to create, populate, and alter a table:

CREATE TABLE T1 (C1 INTEGER NOT NULL); 
INSERT INTO T1 VALUES (1); 
ALTER TABLE T1 ADD COLUMN C2 NOT NULL GENERATED ALWAYS 
  FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP; 
SELECT T1.C2 FROM T1 WHERE T1.C1 = 1;

Because the ROW CHANGE TIMESTAMP column was added after the data was inserted, the following statement returns the time that the page was last modified:

SELECT T1.C2 FROM T1 WHERE T1.C1 = 1;

Assume that you then issue the following statement:

INSERT INTO T1(C1) VALUES (2);

Assume that this row is added to the same page as the first row. The following statement returns the time that value "2" was inserted into the table:

SELECT T1.C2 FROM T1 WHERE T1.C1 = 2;

Because the row with value "1" still does not have a value for the ROW CHANGE TIMESTAMP column, the following statement still returns the time that the page was last modified, which in this case is the time that value "2" was inserted:

SELECT T1.C2 FROM T1 WHERE T1.C1 = 1;