DB2 log (1) Basic knowledge

In the database, the importance of logs is very large, so logs must be understood first.

Log Type:
Primary log (Primary logs)
Pre-allocated. The available quantity is specified by the LOGPRIMARY parameter of db cfg.

Secondary log (Secondary logs)
Dynamically allocate as needed. The maximum number is determined by the LOGSECOND parameter of db cfg.
All connections are closed and deleted.
If LOGSECOND is-1, the log size is not limited.

Log Record type:
There are two types of cyclic logs and archive logs. You can use the "Configure database logging" Wizard to modify the logging type of the database.
(1) Cyclic logs:
The default logging policy, which is used by cyclic logs in sequence. When the jobs in the log have been submitted or rolled back, they can be reused.
Master logs are pre-allocated. Auxiliary logs are allocated only when necessary.

There are two types of cyclic logs: Primary log and secondary log.
The primary log must be configured in advance, and the secondary log can be allocated as needed.
The DB2 administrative program requests in sequence and finds the primary log. The required primary log is unavailable. A secondary log is allocated.
-> The secondary log is full.-> check whether the primary log can be used.-> If the secondary log is unavailable, a new secondary log is allocated.
-> Repeat the preceding check.
-> The secondary log is released until the primary Log File becomes reusable.

The role of cyclic logs in Backup recovery:
Roll-forward recovery is not supported, but crash recovery and version recovery are supported.
Only offline backup is supported.

(2) Archiving logs
Archive logs are a log file management technology that archives logs that are not active.
Archive logs are non-default.
There are several types of archived logs
1. Activity Log
Contains transaction-related information that has not been committed (or rolled back). It also contains transaction-related information that has been committed but has not been modified.
Restart database;
Rollforward
　
2. Online archiving logs
When the activity log is disabled, it becomes an archive log. They are called "online" because they and activities
Logs are stored in the same subdirectory.

3. Offline archiving logs
These log files have been removed from the active log directory. You can either manually or
It is automatically called through a process such as userexit. The archived log file can be
Stored on tape or other media. For example, on tsm.


Active logs (Activity logs)-> If the logs are used up (online archiving logs)-> back up or remove these unused logs (offline archiving logs)

The role of cyclic logs in Backup recovery:
The only LOG method that supports Rollback Recovery and database recovery.
Supports offline and online backup

View the main log-related parameters in the DB2 database:

Whether userexit is enabled
Whether logretain enables archiving logs
Does trackmod enable Incremental backup?
These are off by default, that is, the cyclic log mode. For example, if they are "Yes" or "Recovery", they indicate the archive log mode.

View commands in Unix/Linux:
Db2 get db cfg for sample | grep "userexit"
Db2 get db cfg for sample | grep "logretain"
Db2 get db cfg for sample | grep "trackmod"

View commands in Windows:
Db2 get db cfg for sample | find/I "userexit"
Db2 get db cfg for sample | find/I "logretain"
Db2 get db cfg for sample | find/I "trackmod"

You can change these parameters to make the DB2 database in archive log mode to support online backup.
Change command:
Db2 update db cfg for sample using userexit on // Enable User exit
Db2 update db cfg for sample using logretain on // enable archiving logs
Db2 update db cfg for sample using trackmod on // enable the Incremental backup function

Note:
After changing these archive mode parameters, you cannot connect to the database immediately. You must back up the data offline once.
Offline backup command example:
Db2 backup db sample to c: \ xclbkcold

Log Management:
View the first activity log:
Db2 get db cfg for sample
First active log file = S0000011.LOG // in Unix/Linux
First activity log file = S0000011.LOG // in Windows

Clear logs:
Db2 connectto sample
Db2 prune logfile prior to S0000011.LOG
S0000011.LOG is the first activity log.

Manual archiving command:
Db2 archive log for db sample // logs are generated manually

Recommended reading:

DB2 database performance adjustment and optimization (1st and 2) PDF

DB2 database performance optimization

DB2 logs (2) use C language extensions to implement DB2 log management and master-slave synchronization.