All databases maintain log files that keep
records of database changes. There are two logging strategy choices:
- Circular logging, in which the log
records fill the log files and then overwrite the initial log records in
the initial log file. The overwritten log records are not recoverable.
- Retain log records, in which a log
file is archived when it fills with log records. New log files are made
available for log records. Retaining log files enables
roll-forward recovery. Roll-forward
recovery reapplies changes to the database based on completed units of
work (transactions) that are recorded in the log. You can specify that
roll-forward recovery is to the end of the logs, or to a particular
point in time before the end of the logs.
Regardless of the logging strategy, all
changes to regular data and index pages are written to the log buffer. The
data in the log buffer is written to disk by the logger process. In the
following circumstances, query processing must wait for log data to be
written to disk:
- On COMMIT
- Before the corresponding data pages
are written to disk, because DB2 uses write-ahead logging. The benefit
of write-ahead logging is that when a transaction completes by executing
the COMMIT statement, not all of the changed data and index pages need
to be written to disk.
- Before some changes are made to
metadata, most of which result from executing DDL statements
- On writing log records into the log
buffer, if the log buffer is full.
DB2 manages writing log data to disk in
this way in order to minimize processing delay. In an environment in which
many short concurrent transactions occur, most of the processing delay is
caused by COMMIT statements that must wait for log data to be written to
disk. As a result, the logger process frequently writes small amounts of log
data to disk, with additional delay caused by log I/O overhead. To balance
application response time against such logging delay, set the
mincommit database configuration parameter to a value
greater than 1.
This Changes to large objects (LOB) and
LONG VARCHAR are tracked through shadow paging. LOB column changes are not
logged unless you specify log retain and the LOB column is defined on the
CREATE TABLE statement without the NOT LOGGED clause. Changes to allocation
pages for LONG or LOB data types are logged like regular data pages.