Logging overhead

The database manager maintains log files that record all database changes. There are two logging strategies: circular logging and archive logging.

  • With circular logging, log files are reused (starting with the initial log file) when the available files have filled up. The overwritten log records are not recoverable.
  • With archive logging, log files are archived when they fill up with log records. Log retention enables rollforward recovery, in which changes to the database (completed units of work or transactions) that are recorded in the log files can be reapplied during disaster recovery.
All changes to regular data and index pages are written to the log buffer before being written to disk by the logger process. SQL statement processing must wait for log data to be written to disk:
  • On COMMIT
  • Until the corresponding data pages are written to disk, because the Db2® server uses write-ahead logging, in which not all of the changed data and index pages need to be written to disk when a transaction completes with a COMMIT statement
  • Until changes (mostly resulting from the execution of data definition language statements) are made to the metadata
  • When the log buffer is full

Db2 manages writing of log data to disk, including I/O initiation and data amounts, in order to minimize the overall SQL statement processing time.

Changes to large objects (LOBs) and LONG VARCHARs are tracked through shadow paging. LOB column changes are not logged unless you specify log retain and the LOB column has been defined without the NOT LOGGED clause on the CREATE TABLE statement. Changes to allocation pages for LONG or LOB data types are logged like regular data pages. Inline LOB values participate fully in update, insert, or delete logging, as though they were VARCHAR values.