Scenario: Time-based update detection

This scenario demonstrates how time-based update detection is implemented in an application program using update detection by timestamp, covering three different scenarios.

In this scenario, the application selects all rows that have changed in the last 30 days.

    SELECT * FROM TAB WHERE 
      ROW CHANGE TIMESTAMP FOR TAB <= 
      CURRENT TIMESTAMP AND
      ROW CHANGE TIMESTAMP FOR TAB >= 
      CURRENT TIMESTAMP - 30 days;

Scenario 1:

No row change timestamp column is defined on the table. Statement fails with SQL20431N. This SQL expression is only supported for tables with a row change timestamp column defined.

Note: This scenario will work on z/OS®.

Scenario 2:

A row change timestamp column was defined when the table was created:

    CREATE TABLE TAB ( ..., RCT TIMESTAMP NOT NULL 
                          GENERATED ALWAYS 
                          FOR EACH ROW ON UPDATE AS
                          ROW CHANGE TIMESTAMP)

This statement returns all rows inserted or updated in the last 30 days.

Scenario 3:

A row change timestamp column was added to the table using the ALTER TABLE statement at some point in the last 30 days:

    ALTER TABLE TAB ADD COLUMN RCT TIMESTAMP NOT NULL 
                                   GENERATED ALWAYS 
                                   FOR EACH ROW ON UPDATE AS
                                   ROW CHANGE TIMESTAMP

This statement returns all the rows in the table. Any rows that have not been modified since the ALTER TABLE statement will use the default value of the timestamp of the ALTER TABLE statement itself, and all other rows that have been modified since then will have a unique timestamp.