What are the start points of the DB2 archiving log management solution?

This article describes how to manage the archive logs of DB2. Analyze the problem and describe the DB2 archiving log management solution using the actual operation method of the USEREXIT program. The following describes the main content of this article.

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 manage database logs (manually copy online archived logs to an archive directory) or write the USEREXIT program to automatically maintain database logs.

Since DB2 provides the corresponding USEREXIT sample program for all platforms and is very easy to modify and use, IBM recommends that you use the USEREXIT program to archive log files in DB2.

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 (c: \ mylogs by default in the program on windows ).

 
 

  
  
#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. The above content is an introduction to the DB2 archive log management solution. I hope you will get some benefits.