How Will Logging Process happen in DB2?

What is a Log:

Log is nothing but a transaction log. A transaction log is a file which stores the history of actions executed by a database management system, which is used to recover at the time of crashes.

All databases maintain log files that keep records of database changes.

Types of logging strategy choices:

1. Circular logging
2. Retain log records

Circular Logging: 

In circular logging, the log files are filled with log records and overwrite the initial log records when there is no active transaction on the initial log file.

For example, let's consider a database which is using circular logging strategy. The number of log files in that database is limited to four. Let's assume an application is connected to the database and making some changes. The transaction occupies all the four log files but the work not yet completed. There are no log files left for the transaction, according to our circular logging rule it should overwrite the initial log file, but it will not be allowed here because the 1st log file is still in an active state. Once the transaction is committed then only the transaction can overwrite the log file in circular logging.

In a huge database where huge transactions happen, there this circular logging doesn't work. So to overcome this we have another logging strategy that is Retain log records.

Retain log records:

In this, once the log files are filled with log records, it will be archived and new log files are made available for the transaction. Retaining log files enables rollforward recovery. With roll forward recovery we can reapply changes to the database based on completed units of work which are recorded in the log.

When log data are written to disk:

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

In order to minimise processing delay, db2 manages writing logs to disk.
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 an 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 setting might cause a longer delay for COMMIT from some applications, but more log data might be written in one operation.

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 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.