Finding rows that were changed within a specified period of time

You can filter rows based on the time that they were updated. For example, you might want to find all rows in a particular table that have been changed in the last 7 days.

Procedure

To find the rows that were changed within a specified period of time:

Specify the ROW CHANGE TIMESTAMP expression in the predicate of your SQL statement.
Recommendation: Ensure that the table has a ROW CHANGE TIMESTAMP column that was defined prior to the time period that you want to query. This column ensures that Db2 returns only those rows that were updated in the given time period.

If the table does not have a ROW CHANGE TIMESTAMP column, Db2 returns all rows on each page that has had any changes within the given time period. In this case, your result set can contain rows that have not been updated in the give time period, if other rows on that page have been updated or inserted.

Examples

Example
Suppose that the TAB table has a ROW CHANGE TIMESTAMP column and that you want to return all of the records that have changed in the last 30 days. The following query returns all of those rows.
SELECT * FROM TAB
WHERE ROW CHANGE TIMESTAMP FOR TAB <= CURRENT TIMESTAMP AND
ROW CHANGE TIMESTAMP FOR TAB >= CURRENT TIMESTAMP - 30 days;
Example
Suppose that you want to return all of the records that have changed since 9:00 AM January 1, 2004. The following query returns all of those rows.
SELECT * FROM TAB
WHERE ROW CHANGE TIMESTAMP FOR TAB >= '2004-01-01-09.00.00';