DB2 10.5 for Linux, UNIX, and Windows

ROW CHANGE expression

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


row-change-expression

|--ROW CHANGE--+-TOKEN-----+--FOR--table-designator-------------|
               '-TIMESTAMP-'

TOKEN: Specifies that a BIGINT value representing a relative point in the modification sequence of a row is to be returned. If the row has not been changed, the result is a token that represents when the initial value was inserted. The result can be null. ROW CHANGE TOKEN is not deterministic.
TIMESTAMP: Specifies that a TIMESTAMP value representing the last time that a row was changed is to be returned. If the row has not been changed, the result is the time that the initial value was inserted. The result can be null. ROW CHANGE TIMESTAMP is not deterministic.
FOR table-designator: Identifies the table in which the expression is referenced. The table-designator must uniquely identify a base table, view, or nested table expression (SQLSTATE 42867). If table-designator identifies a view or a nested table expression, the ROW CHANGE expression returns the TOKEN or TIMESTAMP of the base table of the view or nested table expression. The view or nested table expression must contain only one base table in its outer subselect (SQLSTATE 42867). If the table-designator is a view or nested table expression, it must be deletable (SQLSTATE 42703). For information about deletable views, see the "Notes" section of "CREATE VIEW". The table designator of a ROW CHANGE TIMESTAMP expression must resolve to a base table that contains a row change timestamp column (SQLSTATE 55068).

Notes

ROW CHANGE TOKEN and ROW CHANGE TIMESTAMP are not valid expressions for a column-organized table (SQLSTATE 42703).

Return a timestamp value that corresponds to the most recent change to each row from the EMPLOYEE table for employees in department 20. Assume that the EMPLOYEE table has been altered to contain a column defined with the ROW CHANGE TIMESTAMP clause.
```
   SELECT ROW CHANGE TIMESTAMP FOR EMPLOYEE
     FROM EMPLOYEE WHERE DEPTNO = 20
```
Return a BIGINT value that represents a relative point in the modification sequence of the row corresponding to employee number 3500. Also return the RID_BIT scalar function value that is to be used in an optimistic locking DELETE scenario. Specify the WITH UR option to get the latest ROW CHANGE TOKEN value.
```
   SELECT ROW CHANGE TOKEN FOR EMPLOYEE, RID_BIT (EMPLOYEE)
     FROM EMPLOYEE WHERE EMPNO = '3500' WITH UR
```
The preceding statement succeeds whether or not there is a row change timestamp column in the EMPLOYEE table. The following searched DELETE statement deletes the row specified by the ROW CHANGE TOKEN and RID_BIT values from the preceding SELECT statement, assuming the two parameter marker values are set to the values obtained from the preceding statement.
```
   DELETE FROM EMPLOYEE E
     WHERE RID_BIT (E) = ? AND ROW CHANGE TOKEN FOR E = ?
```