Level: Intermediate John Casey (jcasey@us.ibm.com), Consulting IT Software Specialist , IBM Corporation
11 Jan 2007 In DB2® for Linux, UNIX®, and Windows®, log files are primarily used to provide mechanisms for managing transactions and data recovery. But they also play a key role in SQL replication. In this article, learn how to manage database logs in the SQL replication environment using the DB2 database LOGRETAIN parameter set to CAPTURE.
Introduction
If you're a DBA, you know that one of your main responsibilities is to manage log files. Log files record all of the SQL statements that transpire in each transaction, to enable recovery in the event that the transaction is interrupted for some reason. In DB2® for Linux, UNIX®, and Windows®, the LOGRETAIN database parameter is the primary tool for managing logs. This parameter defines the recoverability of a DB2 database and determines how log files are managed. This article explains when LOGRETAIN should be set to the CAPTURE value, and how to manage the DB2 log files in this environment.
Before starting this discussion, let's quickly review the LOGRETAIN options to make sure you understand the
functions and requirements of each option. This discussion is intended to help you determine whether the LOGRETAIN value of CAPTURE
is appropriate for your environment. Also, a good understanding of SQL replication is assumed (see the Resources section).
The default value for the LOGRETAIN parameter is NO. The NO option provides support for logical unit of work backout, but does not provide support for
roll-forward recovery. The log files are managed in a circular manner. This option can be set by either the UPDATE DATABASE CONFIGURATION
command or through the DB2 Control Center.
The other documented option for LOGRETAIN is RECOVERY. The RECOVERY option provides support for both logical unit of work
backout and roll-forward recovery. The log files are retained to support roll-forward recovery, and you must manage
the storage and disposition of the log files. This option can be set by either the UPDATE DATABASE CONFIGURATION
command or through the DB2 Control Center. When you change the LOGRETAIN option to RECOVERY, you must take a database backup before the database is usable.
The third and undocumented option for the LOGRETAIN parameter is CAPTURE. The CAPTURE option provides support for logical unit
of work backout, but does not provide support for roll-forward recovery. The difference between the NO and CAPTURE options is in how
the log files are managed. With the CAPTURE option, the log files are retained so that the DB2 SQL replication Capture program can
read changes from the log files. This option does not require a database backup, and the log files need only be retained until
the SQL replication program Capture finishes processing the changes contained in the log files. Also, you can only set this option with the UPDATE DATABASE CONFIGURATION command.
As you can infer from the name, this option is only of value when using DB2 SQL replication, when there is no need for
roll-forward recovery. Environments in which you have no need for roll-forward recovery include but are not limited to:
- An environment used as a test or sandbox environment that uses SQL replication
- An SQL replication replica environment, where SQL replication does a full refresh from the source (providing recovery)
- An environment that does not contain the necessary resources for roll-forward recovery, such as dedicated physical devices
for the log files that use SQL replication
If you are not using SQL replication, there is no reason to use the CAPTURE option for the LOGRETAIN parameter. A mobile user with a DB2 database on a laptop would commonly employ the CAPTURE option, if using SQL replication to update anywhere subscriptions (and this is the replica site).
The remainder of this article assumes LOGRETAIN is set to CAPTURE. We'll discuss how to determine when the SQL replication
Capture program is finished using a log file, and then use this information to purge the log files no longer needed by Capture.
Note: The information and techniques described in this article were verified using DB2 for Windows Version 8 and Version 9.
Determine the log files no longer needed by Capture
In the MIN_INFLIGHTSEQ column of the ASN.IBMSNAP_RESTART table, Capture maintains the logical log sequence number at which
it starts during a warm restart. This value represents the earliest log sequence number that the Capture program found for
which a commit or abort record has not yet been found in the log files. When you use this information as input to the DB2FLSN
command (Find Log Sequence Number), the command returns the name of the log file that contains this logical log sequence number.
This command can be built with the following SQL statement:
SELECT 'DB2FLSN' CONCAT LCASE(SUBSTR(HEX(MIN_INFLIGHTSEQ),9,12))
FROM ASN.IBMSNAP_RESTART
|
You must issue the DB2FLSN command from the database path that contains the log file header (LFH) file. If the database is active, you can extract
the name of the database path from the output of the DB2 LIST ACTIVE DATABASES command.
Purge the log files no longer needed by Capture
You can then use the output of the DBFLSN command as the name of the log file in the DB2 command PRUNE LOGFILE PRIOR TO log_file_name. This command deletes all log files prior to the starting log file that Capture needs when performing a warm start. You should purge
log files periodically to ensure disk space is not exhausted.
An example of how to automate the purging of log files
As you can see from the two previous sections, purging the log files no longer needed by the SQL replication Capture program is
delicately intertwined with the information maintained by Capture and several DB2 commands. Purging manually, without error,
is a daunting task. To ensure accurate execution of this task every time, the purge should be automated. This example shows you how to automate the purging of log files that are no longer needed by Capture when using LOGRETAIN CAPTURE in a Windows environment.
This example is built upon two Windows BAT files and two small Java™ programs.
First BAT file: Reclaim_Log.bat
Since a DB2 Command environment is needed to execute DB2 commands, the first BAT file sets up the execution of the second BAT file in
a DB2 Command environment. By doing this, the automated process can be started in multiple ways within a Windows environment. For instance, it can be started from
the Startup folder. This BAT file requires three parameters: the name of the database, your user ID, and your password.
You must be authorized and have the necessary privileges to execute the DB2 Commands. The following is the content of this BAT file:
Contents of the Reclaim_Log.bat file
rem create needed db2cmd environment
db2cmd -c -w -i reclaim_log_x.bat %1 %2 %3
rem exit
|
In this example, the name of this BAT file is Reclaim_Log.bat and would be initiated with the RECLAIM_LOG database_name user_name user_password command.
The RECLAIM_LOG.BAT batch command file sets up the DB2 Command environment that will execute the RECLAIM_LOG_X.BAT batch command file.
Second BAT file: Reclaim_LogX.bat
The second BAT file, Reclaim_LogX.bat, contains the commands to perform the following:
- Save a history of the last seven executions per database
- Execute the first Java program
- Execute the BAT file generated by the first Java program
- Execute the second Java program
- Execute the BAT file generated by the second Java program
Contents of the Reclaim_Log_X.bat file
erase %1_act_db.ou7
rename %1_act_db.ou6 %1_act_db.ou7
rename %1_act_db.ou5 %1_act_db.ou6
rename %1_act_db.ou4 %1_act_db.ou5
rename %1_act_db.ou3 %1_act_db.ou4
rename %1_act_db.ou2 %1_act_db.ou3
rename %1_act_db.out %1_act_db.ou2
erase %1_log_name.tx7
rename %1_log_name.tx6 %1_log_name.tx7
rename %1_log_name.tx5 %1_log_name.tx6
rename %1_log_name.tx4 %1_log_name.tx5
rename %1_log_name.tx3 %1_log_name.tx4
rename %1_log_name.tx2 %1_log_name.tx3
rename %1_log_name.txt %1_log_name.tx2
erase %1_reclaimlog2.ba7
rename %1_reclaimlog2.ba6 %1_reclaimlog2.ba7
rename %1_reclaimlog2.ba5 %1_reclaimlog2.ba6
rename %1_reclaimlog2.ba4 %1_reclaimlog2.ba5
rename %1_reclaimlog2.ba3 %1_reclaimlog2.ba4
rename %1_reclaimlog2.ba2 %1_reclaimlog2.ba3
rename %1_reclaimlog2.bat %1_reclaimlog2.ba2
erase %1_reclaimlog4.ba7
rename %1_reclaimlog4.ba6 %1_reclaimlog4.ba7
rename %1_reclaimlog4.ba5 %1_reclaimlog4.ba6
rename %1_reclaimlog4.ba4 %1_reclaimlog4.ba5
rename %1_reclaimlog4.ba3 %1_reclaimlog4.ba4
rename %1_reclaimlog4.ba2 %1_reclaimlog4.ba3
rename %1_reclaimlog4.bat %1_reclaimlog4.ba2
JAVA ReclaimLog1 %1 %2 %3
call %1_reclaimlog2.bat
JAVA ReclaimLog3 %1 %2 %3
%1_reclaimlog4.bat
|
First Java program: ReclaimLog1.java
The ReclaimLog1.java program does the following:
- Connects to the database
- Builds the DB2FLSN command base upon the content of the
MIN_INFLIGHTSEQ column of the ASN.IBMSNAP_RESTART table
- Issues the DB2 LIST ACTIVE DATABASES command and pipes the output to a file named ACT_DB.OUT
- Parses the ACT_DB.OUT file to extract the database path for the DB2FLSN command
- Verifies that LOGRETAIN is set to CAPTURE
- Creates a BAT file, ReclaimLog2.bat, that executes a set of commands which saves the current path in a variable named OLDDIR;
changes the path to the appropriate database path; executes the DB2FLSN command and pipes the output to a file named
Log_Name.txt in the original path; and changes the path back to the original path
After this Java program is executed, the BAT file it generated is executed.
BAT file generated by the first Java program
This is an example of the contents of the ReclaimLog2.bat file:
ReclaimLog2.bat
SET OLDDIR=%CD%
cd /d C:\DB2\NODE0000\SQL00006\
DB2FLSN -q 000012ce08e5 > %OLDDIR%\SOURCE_LOG_NAME.TXT
CD /D %OLDDIR%
|
After this BAT file is executed, the second Java program is executed.
Second Java Program: ReclaimLog3.java
The ReclaimLog3.java program does the following:
- Connects to the database
- Reads the log name from the Log_Name.txt file
- Creates a BAT file, ReclaimLog4.bat, that executes a set of commands which connect to the database and issue the DB2 PRUGE
LOGFILE command
After this Java program is executed, the BAT file it generated is executed.
BAT file generated by the second Java program
This is an example of the contents of the ReclaimLog4.bat file:
ReclaimLog4.bat
DB2 CONNECT TO SOURCE USER jcasey USING xxxxxxxx
DB2 PRUNE LOGFILE PRIOR TO S0000011.LOG
|
After this BAT file is executed, the unnecessary logs have been pruned and the process is finished.
Conclusion
If you use SQL replication, this article presented another way to manage DB2 logs for distributed platforms
in which roll-forward recovery is not needed or is unsupported. To this end, these topics were covered in this article:
- A description of the undocumented CAPTURE value for the LOGRETAIN parameter
- A discussion of when and how to use the CAPTURE value for the LOGRETAIN parameter
- An example of how to automate the purging of DB2 log files when you use the CAPTURE value for the LOGRETAIN parameter
Appendix: Java program listings
The following sections contain the source code of the Java programs used in the automation example.
Source of ReclaimLog1.java program
import java.lang.*;
import java.io.*;
import java.util.*;
import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
/* ************************************************************************** */
/* */
/* This program Reclaims Log space in an environment where you have specified */
/* LOGRETAIN = CAPTURE */
/* */
/* ************************************************************************** */
class ReclaimLog1 {
static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(-1);
}
}
static String DBName;
static String DB2FLSQ;
static String logPath;
static String stmtID;
static String userID;
static String passWord;
static Connection con;
public static void main(String[] args) {
get_Input_Var (args); /* input variable is database name */
build_Flsq_Cmd (); /* build DB2FLSQ command */
get_Log_dir (); /* get directory for the database */
set_Log_dir (); /* set directory for the database */
is_Capture (); /* ensure LOGRETAIN is set to CAPTURE */
create_bat_file () ; /* create bat file */
}
public static void get_Input_Var( String[] args)
{
DBName = args[0].toUpperCase();
userID = args[1];
passWord = args[2];
String URL = "jdbc:db2:" + DBName;
stmtID = "trying to connect to " + DBName ;
try {
con = DriverManager.getConnection(URL, userID, passWord );
}
catch(Exception e){
System.out.println("Unsuccessful when " + stmtID);
System.out.println(e.getMessage());
System.exit(-8);
}
return;
}
public static void build_Flsq_Cmd ()
{
try {
String sqlValue = "SELECT 'DB2FLSN -q ' CONCAT " +
"LCASE(SUBSTR(HEX(MIN_INFLIGHTSEQ),9,12)) FROM ASN.IBMSNAP_RESTART";
stmtID = "Prepare " + sqlValue;
PreparedStatement stmt = con.prepareStatement(sqlValue );
stmtID = "Execute " + sqlValue;
ResultSet rs = stmt.executeQuery();
rs.next();
stmtID = "Get RS(1) " + sqlValue;
DB2FLSQ = rs.getString(1) + " > %OLDDIR%\\" + DBName + "_LOG_NAME.TXT";
stmtID = "rs.close " + sqlValue;
rs.close();
stmtID = "stmt.close " + sqlValue;
stmt.close();
System.out.println("This is the DB2FLSQ command = " + DB2FLSQ);
return;
}
catch(Exception e){
System.out.println("Unsuccessful when " + stmtID);
System.out.println(e.getMessage());
System.exit(-8);
}
}
public static void get_Log_dir ()
{
String cmdTxt = "db2cmd -c -w -i DB2 LIST ACTIVE DATABASES > " +
DBName + "_ACT_DB.OUT";
doCommand( cmdTxt);
cmdTxt = "db2cmd -c -w -i DB2 GET DB CFG FOR " + DBName + " > " +
DBName + "_DB_CFG.OUT";
doCommand( cmdTxt);
return;
}
public static void doCommand (String cmdTxt)
{
Process tProcess = null;
int rC = 0;
try {
tProcess = Runtime.getRuntime().exec(cmdTxt);
}
catch (Exception e) {
e.printStackTrace();
System.out.println("System exiting...");
System.exit(-1);
}
try {
rC = tProcess.waitFor();
if (rC < 0) {
System.out.println("return code = " + rC);
}
} catch (Exception e) {
System.out.println("Destroying process...");
tProcess.destroy();
System.out.println("System exiting...");
System.exit(-1);
}
tProcess.destroy();
if (rC < 0) {
System.out.println("return code = " + rC);
System.out.println("System exiting...");
System.exit(-1);
}
return;
}
public static void set_Log_dir ()
{
String tblfile=DBName +"_ACT_DB.OUT";
try {
BufferedReader in = new BufferedReader(new FileReader(tblfile));
String line;
while ((line = in.readLine()) != null)
{ // Read line, check for end-of-file
// print the line
StringTokenizer st = new StringTokenizer(line);
if (st.countTokens() > 1) {
if(st.nextToken().equals("Database"))
{
st.nextToken();
st.nextToken();
if(st.nextToken().equals(DBName))
{
line = in.readLine();
line = in.readLine();
line = in.readLine();
line = in.readLine();
st = new StringTokenizer(line);
st.nextToken();
st.nextToken();
st.nextToken();
logPath = "cd /d " +st.nextToken();
System.out.println ("This is the command to change to log path = " + logPath);
in.close(); // Always close a stream when you are done with it
return;
}
}
}
}
in.close(); // Always close a stream when you are done with it
System.out.println("Database not found in ACT_DB.OUT file" );
System.exit(-12);
}
catch (IOException e) {
System.out.println("error in reading or parsing ACT_DB.OUT file" );
System.out.println(e );
System.exit(-12);
}
return;
}
public static void is_Capture ()
{
String tblfile=DBName +"_DB_CFG.OUT";
try{
BufferedReader in = new BufferedReader(new FileReader(tblfile));
String line;
while ((line = in.readLine()) != null)
{System.out.println (line);
if (line.equals
("Log retain for recovery status = CAPTURE"));
{
System.out.println("LOGRETAIN is set to CAPTURE for database "+ DBName );
in.close(); // Always close a stream when you are done with it
return;
}
}
System.out.println("LOGRETAIN not set to CAPTURE for database "+ DBName );
System.exit(-12);
in.close(); // Always close a stream when you are done with it
}
catch (IOException e) {
System.out.println("error in reading or parsing ACT_DB.OUT file" );
System.out.println(e );
System.exit(-12);
}
return;
}
public static void create_bat_file ()
{
String batfile = DBName +"_ReclaimLog2.bat";
try
{
File fout = new File(batfile);
PrintWriter KCout = new PrintWriter(new FileWriter(fout));
String bat_out = "SET OLDDIR=%CD%";
KCout.println(bat_out);
bat_out = logPath;
KCout.println(bat_out);
bat_out = DB2FLSQ;
KCout.println(bat_out);
bat_out = "CD /D %OLDDIR%";
System.out.println("bat_out = " + bat_out);
KCout.println(bat_out);
/* bat_out = "exit";
KCout.println(bat_out); */
KCout.close();
}
catch (IOException e) {
System.out.println(batfile + " error");
System.exit(-12);
}
return;
}
}
|
Source of ReclaimLog3.java program
import java.lang.*;
import java.io.*;
import java.util.*;
import java.sql.*; // JDBC classes
import COM.ibm.db2.jdbc.app.*; // DB2 UDB JDBC classes
/* ************************************************************************** */
/* */
/* This program Reclaims Log space in an environment where you have specified */
/* LOGRETAIN = CAPTURE */
/* */
/* This facility prior to DB2 UDB V8 was a capability of Capture */
/* */
/* ************************************************************************** */
class ReclaimLog3 {
static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(-1);
}
}
static String DBName;
static String DB2FLSQ;
static String logPath;
static String stmtID;
static String userID;
static String passWord;
static String logName;
static Connection con;
public static void main(String[] args) {
get_Input_Var (args); /* input variable is database name */
build_purge_Cmd (); /* build purge command */
create_bat_file () ; /* create bat file */
}
public static void get_Input_Var( String[] args)
{
DBName = args[0].toUpperCase();
userID = args[1];
passWord = args[2];
String URL = "jdbc:db2:" + DBName;
stmtID = "trying to connect to " + DBName ;
try {
con = DriverManager.getConnection(URL, userID, passWord );
}
catch(Exception e){
System.out.println("Unsuccessful when " + stmtID);
System.out.println(e.getMessage());
System.exit(-8);
}
return;
}
public static void build_purge_Cmd ()
{
String tblfile=DBName + "_log_name.txt";
try{
BufferedReader in = new BufferedReader(new FileReader(tblfile));
String line;
logName = in.readLine();
}
catch (IOException e) {
System.out.println("error in reading log_name.txt file" );
System.out.println(e );
System.exit(-12);
}
return;
}
public static void create_bat_file ()
{
String batfile =DBName + "_ReclaimLog4.bat";
try
{
File fout = new File(batfile);
PrintWriter KCout = new PrintWriter(new FileWriter(fout));
String bat_out = "DB2 CONNECT TO " + DBName + " USER " +
userID + " USING " + passWord;
KCout.println(bat_out);
bat_out = "DB2 PRUNE LOGFILE PRIOR TO " + logName;
KCout.println(bat_out);
KCout.close();
}
catch (IOException e) {
System.out.println(batfile + " error");
System.exit(-12);
}
return;
}
}
|
Disclaimer
This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty-free, license to use this sample code.
However, the sample code is provided as-is and without any warranties, whether express or implied, including any implied warranty
of merchantability, fitness for a particular purpose or non-infringement. IBM and its licensors shall not be liable for any
damages suffered by the licensee that results from your use of the software. In no event will IBM or its licensors be liable for any
lost revenue, profit or data, or for the direct, indirect, special, consequential, incidental or punitive damages, however caused
and regardless of the theory of liability, arising out of the use of or inability to use software, even if IBM has been advised of
the possibility of such damages.
Download | Description | Name | Size | Download method |
|---|
| Sample reclaim programs for this article | samples.zip | 10KB | HTTP |
|---|
Resources Learn
Get products and technologies
Discuss
About the author  | 
| | John Casey is a Consulting IT Software Specialist with IBM Corporation in Dallas, TX. |
Rate this page
| 
