Scenarios: Optimistic locking using implicitly hidden columns

The following scenarios demonstrate how optimistic locking is implemented in an application program using implicitly hidden columns, that is, a column defined with the IMPLICITLY HIDDEN attribute.

For these scenarios, assume that table SALARY_INFO is defined with three columns, and the first column is an implicitly hidden ROW CHANGE TIMESTAMP column whose values are always generated.

Scenario 1:

In the following statement, the implicitly hidden column is explicitly referenced in the column list and a value is provided for it in the VALUES clause:

    INSERT INTO SALARY_INFO (UPDATE_TIME, LEVEL, SALARY)
      VALUES (DEFAULT, 2, 30000)

Scenario 2:

The following INSERT statement uses an implicit column list. An implicit column list does not include implicitly hidden columns, therefore, the VALUES clause only contains values for the other two columns:

    INSERT INTO SALARY_INFO
      VALUES (2, 30000)

In this case, column UPDATE_TIME must be defined to have a default value, and that default value is used for the row that is inserted.

Scenario 3:

In the following statement, the implicitly hidden column is explicitly referenced in the select list and a value for it appears in the result set:

    SELECT UPDATE_TIME, LEVEL, SALARY FROM SALARY_INFO
      WHERE LEVEL = 2
      
      UPDATE_TIME        LEVEL    SALARY   
      -------------------------- ----------- -----------
      2006-11-28-10.43.27.560841      2    30000

Scenario 4:

In the following statement the column list is generated implicitly through use of the * notation, and the implicitly hidden column does not appear in the result set:

    SELECT * FROM SALARY_INFO
      WHERE LEVEL = 2
      
      LEVEL    SALARY   
      ----------- -----------
           2    30000

Scenario 5:

In the following statement, the column list is generated implicitly through use of the * notation, and the implicitly hidden column value also appears by using the ROW CHANGE TIMESTAMP FOR expression:

    SELECT ROW CHANGE TIMESTAMP FOR SALARY_INFO 
      AS ROW_CHANGE_STAMP, SALARY_INFO.*  
      FROM SALARY_INFO WHERE LEVEL = 2

The result table will be similar to scenario 3 (column UPDATE_TIME will be ROW_CHANGE_STAMP).