DB2 database archive log management solution

Requirement Description

Because the LOGRETAIN = ON database configuration is used, the database log files will not be automatically deleted or recycled by the database from the active log directory, and more log files will be stored in the active log directory, therefore, you need to archive the log files in this directory.

Problem Analysis

After you set LOGRETAIN to ON, the database supports rollback and recovery. There are three types of log files in the system:

Activity Log: This log contains information about transaction units that have not been committed or rolled back, and the transaction information that has been committed but has not been written into the database file.
Online archiving log: all changes in the activity log are not required for normal processing, that is, when the transactions recorded in the log have been committed and written to the database file, this activity log is converted to online archived logs. They are called online because they are stored in the same directory as activity logs.

Offline archiving logs: these logs are archived offline by copying them from the active log directory to another place. These logs may still be required during database rollback and recovery.

You can manually copy online archived logs to an archive directory by managing database logs. You can also compile the USEREXIT program for automatic maintenance.

DB2 provides the USEREXIT sample program for all platforms and is easy to modify and use. Therefore, IBM recommends that you use the USEREXIT program to manage log files.

How to Use the USEREXIT Program

To use User exit program to automate the archiving and retrieval processes of log files:

1. Set the database cfg parameter logarchmeth1 to USEREXIT

Connect to sample
Update db cfg using logarchmeth1 USEREXIT
Connect reset

2. Create user exit programs. The program must be an executable file named db2uext2. DB2 provides User Exit sample programs on various platforms. You can directly modify the sample file to implement your user exit program.

In a UNIX (R) based system, the program is stored in the sqllib/samples/c directory.

There are four sample user exit programs for UNIX based systems:

• Db2uext2. ctsm

This sample uses Tivoli (R) Storage Manager to archive and retrieve database log files.

• Db2uext2. ctape

This sample uses tape media to archive and retrieve database log files.

• Db2uext2. cdisk

This sample uses the operating system COPY command and disk media to archive and retrieve database log files.

• Db2uxt2. cxbsa

This sample works with the XBSA Draft 0.8 published by the X/Open group. It can be used to archive and retrieve database log files. This sample is only supported on AIX.

• In Windows (R) systems, this file is stored in the sqllib/samples/c directory.

There are two sample user exit programs for Windows operating systems:

• Db2uext2. ctsm

This sample uses Tivoli Storage Manager to archive and retrieve database log files.

• Db2uext2. cdisk

This sample uses the operating system COPY command and disk media to archive and retrieve database log files.

3. Configure the sample program to specify the system directory to which the log file will be archived under the windows platform, and the default program is c: \ mylogs ).

# Define ARCHIVE_PATH "c :\\ mylogs \\"
# Define RETRIEVE_PATH "c :\\ mylogs \\"
# Define AUDIT_ERROR_PATH "c :\\ mylogs \\"

4. Compile the C file

Cl db2uext2. c

5. Copy db2uext2.exe to the/SQLLIB/BIN directory.

6. In db2cmd, type the archive log for db sample user db2admin using duan1980 command to force the log archiving task to be executed. Check whether a log file is archived in the c: \ mylogs directory to verify whether the file is successfully archived.

(