ROW CHANGE expression

A ROW CHANGE expression returns a token or a timestamp that represents the last change to a row.

ROW CHANGE expression

TIMESTAMP

Specifies that a timestamp is returned that represents the last time when a row was changed. If the row has not been changed, the result is the time that the initial value was inserted.

TOKEN

Specifies that a token that is a BIGINT value is returned that represents a relative point in the modification sequence of a row. If the row has not been changed, the result is a token that represents when the initial value was inserted.

FOR table-designator

Identifies the table in which the expression is referenced. table-designator must be an exposed name that uniquely identifies a base table, a view, or a nested table expression of a subselect. If table-designator identifies a view or a nested table expression, the ROW CHANGE expression returns the TIMESTAMP or TOKEN of the base table of the view or the nested table expression. The view or nested table expression must contain only one base table in its outer subselect.

table-designator must not identify:

An alias, a synonym, or a materialized view
A nested table expression that is materialized
A system-period temporal table, if the system time sensitive bind option is set to YES
An archive-enabled table, if one of the following conditions is true:
- For a static statement, the archive sensitive option in effect is YES.
- For a dynamic statement, the archive sensitive option in effect is YES, and the GET_ARCHIVE built-in global variable is set to 'Y'.

The result can be null. The ROW CHANGE TIMESTAMP and ROW CHANGE TOKEN expressions are not deterministic.

Notes

Tables without a row change timestamp column:

For tables without a row change timestamp column, the ROW CHANGE TIMESTAMP expression returns a timestamp value that reflects changes made to the page instead of to the row. This timestamp value indicates that at least one row in the page has changed, but does not indicate which row, or even how many rows, have changed. The ROW CHANGE TIMESTAMP expression might indicate that a row has changed, however, the change might be for other rows in the same page.

In a data sharing environment, the returned timestamp value is based on the LRSN value of the page and reflects the most recent time the page was modified.

In a non-data sharing environment, the returned timestamp value is based on the RBA value of the page. In a non-data sharing environment, changes made to the same page within a half hour of each other might be indistinguishable. For example, issuing the following SELECT statements in a non-data sharing environment will possibly return the same value, even though the row was changed between the two SELECT statements:

CREATE TABLE T1 (C1 INTEGER NOT NULL);
INSERT INTO T1 VALUES (1);
SELECT ROW CHANGE TIMESTAMP FOR T1 FROM T1;
UPDATE T1 SET C1 = 2 WHERE C1 = 1;
SELECT ROW CHANGE TIMESTAMP FOR T1 FROM T1;

Example 1:

The following example returns all the rows that have been changed in the last day:

SELECT * FROM ORDERS 
  WHERE ROW CHANGE TIMESTAMP FOR ORDERS > 
    CURRENT TIMESTAMP - 24 HOURS;

Example 2:

The following example returns a timestamp value that corresponds to the most recent change to each row from the EMP table for those employees in department 20:

   SELECT ROW CHANGE TIMESTAMP FOR EMP
     FROM EMP WHERE DEPTNO = 20;

Example 3:

The following example returns a BIGINT value that corresponds to a relative point in the modification sequence of EMP with employee number '3500':

   SELECT ROW CHANGE TOKEN FOR EMP
     FROM EMP WHERE EMPNO = '3500';