Additional settings for DB2 database features

Use these settings for DB2® database.

Feature Description Value

Currently Committed Semantics

The DB2 database used the default Cursor Stability (CS) isolation level in all versions until DB2 9.7. If an application changed a row and another application tried to read that row before the first application committed the changes, the second application waited until the commit.

You can now set the currently committed semantics of the CS level, which informs DB2 that when the second application tries to read a locked row, it will get an image of what the row looked like before committing the change.

In the enhanced currently committed semantics, only committed data is returned, as it was in previous versions. However, now the read operation does not wait for the write operation to release the row locks. Instead, the read operation returns the data before the start of the write operation.

The currently committed semantics is turned on by default in the new DB2 9.7 database. The new database configuration parameter, cur_commit is used to override this behavior. For more information, see the Currently committed semantics improve concurrency topic in the DB2 for Linux UNIX and Windows IBM Documentation.

Currently committed semantics requires more log space for write operations. In addition, extra space is required for logging the first update of a data row during a transaction, which can have an insignificant or measurable impact on the total log space used.

The currently committed semantics feature has the following limitations:
  • The target table that is used for a data update or delete operation does not use currently committed semantics.
  • An uncommitted modification to a row forces the currently committed read operation to access appropriate log records and determine the currently committed version of the row. Although log records that are no longer present in the log buffer can be physically read, currently committed semantics does not support the retrieval of log files from the log archive.
  • The following scans do not use currently committed semantics:
    • Catalog table scans
    • Referential integrity constraint enforcement scans
    • LONG VARCHAR or LONG VARGRAPHIC column reference scans
    • Range-clustered table (RCT) scans
    • Spatial or extended index scans

ON