DB2 backup and archive setting

Similar to Oracle, DB2 is also divided into two modes, log cycle vs archive log, which is non-archive and archive mode. The two modes are briefly introduced below.

Circular logging

Circular logging is the default mode, that is, non-archive mode. This mode only supports backup offline, and DB2 needs to stop the service during the backup process.

The following points need to be paid attention to when running offline backup:

1. Stop the application's access to DB2.

2. View the existing connection through the command:

C:Documents and SettingsAdministrator>db2 list application

 Authorization ID Application Name Application Application ID Database Agent

program

                        Handle name serial number

-------- -------------- ---------- ------------------ ---------------------------

----------------- -------- -----

ADMINIS> db2bp.exe 25 *LOCAL.DB2.120726140024

                  TWHIS 1

 Then use the command to end the connection:

db2 => FORCE APPLICATION all

The DB20000I FORCE APPLICATION command completed successfully.

DB21024I This command is asynchronous and may not take effect immediately.

3. Use the DEACTIVATE DATABASE command to ensure that the database is not active.

db2 => DEACTIVATE DATABASE TWHIS

SQL1496W The release of the database was successful, but the database was not activated.

4. Back up the database through the BACKUP DATABASE command.

Archive log

The archive log is not in the default state and needs to be configured to take effect. When DB2 uses the log retention mode, the database is a recoverable database and supports online backup, roll-forward recovery, and crash recovery.

Configuring DB2 to enter the archive log mode is mainly based on the two parameters of Logarchmeth1 and Logarchmeth2 (Note: The Logretain parameter has been replaced by Logarchmeth1 after DB2 v8, so you can ignore it).

Logarchmeth1 is set to LOGRETAIN

With archive logs, the database is a recoverable database. Enable roll-forward recovery and crash recovery, but not in automatic archiving mode.

After the log files are archived, the useless archive logs must be manually deleted so that the new log files can reuse the disk space. Whenever the log file is full, DB2 will start to write records to another log file and continue to create new log files.

Logarchmeth1 is set to a value other than OFF or LOGRETAIN

Use archive logs. The database is a recoverable database. Enable roll-forward recovery and crash recovery. When the log file is full, it is automatically archived, and the destination of the archive is the location set by Logarchmeth1.

If an error occurs while archiving log files, the archiving is suspended for a period of time, which is specified by the ARCHRETRYDELAY database configuration parameter. You can also use the NUMARCHRETRY database configuration parameter to specify the number of times that DB2 tries to archive log files to the primary or secondary archive directory before it attempts to archive log files to the failover directory (specified by the FAILARCHPATH database configuration parameter).

Logarchmeth1 and Logarchmeth2 configurations may have the following combinations

1. Logarchmeth1 is set to LOGRETAIN, Logarchmeth2 can only be set to OFF

     The location of the archive log is the location of the DB2 database log, which requires manual intervention in the transfer and space maintenance of the archive log

2. Logarchmeth1 is set to USEREXIT, Logarchmeth2 can only be set to OFF

     The management of archive logs is handled by USEREXIT. By setting and compiling USEREXIT, a more complex archive management method can be realized.

3. Logarchmeth1 is set to <Directory>, Logarchmeth2 is set to OFF

     The work of archiving logs will be performed automatically, and the required archive logs will be automatically archived to the location specified by <Directory>. Since the archiving is performed automatically, there are only normal logprimary + logsecond database logs in the DB2 log directory.

4. Logarchmeth1 is set to <Directory1>, Logarchmeth2 is set to <Directory2>

     The work of archiving logs will be carried out automatically, and the required archive logs will be automatically archived to the location specified by <Directory1> and <Directory2>, that is, two archive logs will be generated. Since the archiving is performed automatically, the DB2 log directory is only normal logprimary + logsecond database logs.

     Either <Directory1> or <Directory2> can be set to TSM. It is generally recommended that <Directory1> is the file system, and <Directory2> is set to TSM, which can be archived to TSM offline for storage, and can also be used online to archive logs in the file system, which is more convenient.

Note: After setting Logarchmeth1 and Logarchmeth2, the database will enter the backup pending state, and an offline backup must be performed before the data will enter the recovery mode and work normally.

Other commonly used log setting parameters

1. Failover archive path (failarchpath)

If the specified log archiving method fails, specify an alternate directory for archive log files. Before the failed log archiving method is available again, this directory is a temporary storage for log files. At this time, the log files will be moved from this directory to the log archiving method. By moving the log files to this temporary location, you can avoid the log directory being full. This parameter must be a standard existing directory.

If tsm is used for archiving purposes, this parameter is more necessary. When tsm has a problem and cannot receive archived file data, this can be an emergency to prevent the database from hanging.

2. Log file size (logfilsiz)

This parameter specifies the size of each configuration log in 4 KB pages.

If logfilsiz is too small, it will cause frequent log switching and archiving, and when large transactions are encountered (logprimary + logsecond) × logfilsiz is not enough to load all the content, the transaction will fail and roll back.

3. The maximum number of logs per transaction (max_log)

This parameter indicates the percentage of main log space that can be consumed by a transaction. The value is a percentage of the value specified for the logprimary configuration parameter.

If the value is set to 0, there is no limit to the percentage of the total main log space that can be consumed by a transaction. If the application violates the max_log configuration, the application will be forced to disconnect from the database, the transaction will be rolled back, and error SQL1224N will be returned.

If the transaction size cannot be estimated, it is generally set to 0 to avoid accidental rollbacks.

4. primary log (logprimary)

This parameter specifies the number of primary logs of size logfilsiz that will be created. The default is 3

The main log, whether it is empty or full, requires the same amount of disk space. Therefore, if you configure more logs than needed, it will take up disk space unnecessarily. If you configure too few logs, you may encounter a situation where the logs are full. When choosing the number of logs to be configured, you must consider the size of each log created and whether the application can handle the full log load. The total log file size limit for active log space is 256 GB.

5. second logo (logsecond)

This parameter specifies the number of auxiliary log files created and used for recovery (if needed). The default is 2

If the main log file is full, you can allocate one auxiliary log file (size logfilsiz) at a time as needed, up to the maximum number specified by this parameter. If this parameter is set to -1, the database is configured for unlimited active log space. There are no restrictions on the size or number of outstanding transactions running on the database. In environments where large jobs must be accommodated (these jobs require more log space than is usually allocated to the main log), unlimited activity logging is very useful.