![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
©2003 International Business Machines Corporation. All rights reserved.
DB2 Universal DatabaseTM uses log files as a primary means of ensuring the consistency and recoverability of data. In an earlier article, An Overview of Transactional Logging in DB2 Universal Database, I introduced transactional logging concepts and explained how logs are managed. In this article I'll take you a step further, and explain how DB2 UDB can use a user exit program to archive and retrieve database log files. In addition, I'll provide a step-by-step example to explain how to modify, compile, and test the db2uext2.cdisk sample user exit program that comes with your installed DB2 UDB server product. You'll find links to additional resources covering user exit programming with DB2 UDB at the bottom of the article.
This article covers DB2 V8.1 Enterprise Server Edition fix pack 1 (single-partition) server on Windows 2000 and AIX 5L. Although other sample user exit programs are available such as user exit to tape or Tivoli Storage Manager, I do not cover them here.
How does a user exit work with DB2 UDB?
The basic idea behind using a user exit program with DB2 UDB is to provide a method for archiving and retrieving database log files to enable log redundancy and movement away from volatile media. It is important to note that you can implement other operations besides archiving and retrieving logs within the user exit, based on your specific needs.
Having a user exit strategy with DB2 UDB will not recover 100% of your transactions if a database needs to be recovered using archived log files. A user exit program is merely a means to provide more protection for your existing log files by copying them off to a safe location. It's one part of your data integrity strategy, but an important part.
After compiling a user exit program, the db2uext2 executable is placed in a directory where the database manager can find it. This directory is
<instance home>/sqllib/adm
on UNIX©
and
<drive letter>\Program Files\IBM\SQLLIB\BIN on Windows©.
The database manager will not call db2uext2 unless it is aware that a user exit program is available. The only way the database manager will know that the db2uext2 can be called is by setting the database configuration parameter userexit to on. Once this parameter is set and the DB2 instance is recycled, the database manager will make a call every five minutes to the user exit program to check for log files that can be archived to the program-specified archive directory.
If a database recovery is necessary, the database manager will call db2uext2 during the roll forward operation to copy the archived log files back into the active log directory. The log files are then re-applied to the restored database.
Let's take a look at the calling format made by the database manager to the user exit program. Note that this information can also be found in the comments sections of the user exit sample program:
db2uext2 -OS<os> -RL<release> -RQ<request> -DB<dbname> -NN<nodenumber> -LP<logpath> -LN<logname> [-AP<adsmpasswd>] |
where:
os = operating system
release = DB2 release
request = 'ARCHIVE' or 'RETRIEVE'
dbname = database name
nodenumber = node number
logpath = log file path
logname = log file name
logsize = log file size (optional)
startingpage = starting offset in 4K page unit (optional)
adsmpasswd = ADSM password (optional)
Note: logsize and startingpage are only used when logpath is a raw device.
Log files are archived and retrieved from disk with the following naming convention:
archive: archive path + database name + node number + log file name
retrieve: retrieve path + database name + node number + log file name
For example: If the archive path is "c:\mylogs",
the retrieve path is "c:\mylogs",
the database name is "SAMPLE",
the node number is NODE0000,
file name is S0000001.LOG,
the log file would be:
archived to - c:\mylogs\SAMPLE\NODE0000\S0000001.LOG
retrieved from - c:\mylogs\SAMPLE\NODE0000\S0000001.LOG
Here's how the logic flows within the user exit:
1) Install signal handlers.
2) Verify the number of parameters passed.
3) Verify the action requested.
4) Start the audit trail ( if requested ).
5) Take one of the following paths based on the action requested:
a) If the requested action is to archive a file, copy the log file from the log path to the archive path.
i) If the log file is not found proceed to point 6.
b) If the requested action is to retrieve a file, copy the log file from the retrieve path to the log path.
i) If the log file is not found proceed to point 6.
6) Log errors (if requested and required).
7) End the audit trail (if requested).
8) Exit with the appropriate return code.
It is possible for you to manually call the user exit program to archive a log file, but it's better to use the ARCHIVE LOG command so that mistakes are not made on your behalf when specifying the above parameters. A link for the ARCHIVE LOG command can be found at the bottom of this article.
The basic function of a user exit program with DB2 is to copy log files to and from the active log directory. It is worth mentioning some terminology here to clarify where the active log directory is located and the states of database log files.
This directory is located in your database directory. On Windows, if a single database called SAMPLE is created on C:\ and the instance name is db2inst1, the following directory structure will exist:
C:\DB2INST1\NODE0000\SQL000001\SQLOGDIR |
SQL00001 is the database directory for the SAMPLE database and SQLOGDIR is the active log directory.
Figure 1 below shows the active log directory on a Windows operating system:
Figure 1. Active log directory
Within the active log directory, log files can be either active logs or online archived logs. Active logs are those logs needed by DB2 for current transaction processing and crash recovery. Online archived logs are logs no longer needed by DB2 UDB for regular processing but may be needed during a database recovery. When implementing a user exit program, these online archived logs should eventually show up as a copy in your archive log directory.
Since the purpose of a user exit program with DB2 UDB is to copy database logs to an archive directory, you will end up with duplicate log files in your active log directory, by default, SQLOGDIR. You may consider removing these duplicate online archived logs to free up file system space. Be very careful to verify that these logs were successfully copied to the archive directory before removing them from the database directory. You must also make sure they are no longer needed by the database manager for crash recovery. To determine which log files are no longer needed for normal processing in your active log directory, check the database configuration with the following command:
db2 "get db cfg for sample" |
The database configuration output from this command will include the first active log file, for example:
First active log file = S000009.LOG |
The log file
S000009.LOG, shown in the output above, is the currently active log for the database. Any log files that are numbered less than this log file are considered online archived logs.
Here is an example:
In this scenario, the active log directory holds log files
S000000.LOG - S000009.LOG
and the archive log directory holds
S000000.LOG - S000008.LOG. Because
S000009.LOG
is the first active log file,
S000001.LOG - S000008.LOG
can be deleted from the active log directory to free up disk space. The
S000009.LOG
file must be left in the active log directory since it is still being used for current transactions.
The database history file can also be checked to see which log files are no longer required in the active log directory. The following command will list database backup information:
db2 "list history backup all for database sample" |
Here's an example of output from that command:
List History File for sample Number of matching file entries = 4 Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log -- --- ------------------ ---- --- ------------------------ B D 20030416162026001 F D S0000010.LOGS0000014.LOG ------------------------------------------------------------ Contains 2 tablespace(s): 00001 SYSCATSPACE 00002 USERSPACE1 ------------------------------------------------------------ |
In the above output, the earliest log above will signify that any log after and including the
S0000010.LOG
is needed. Any log before
S00000010.LOG
can be safely deleted. Again, it is important to verify that a copy of the log files exist in the archive log directory before deleting those logs from the active log directory.
Although manually deleting log files from the active log directory is possible, a safer way to remove online archived log files is through the
prune logfile
command. This command can be used to delete log files in the active log directory. In the example below, the following command will delete log files
S000000.LOG - S000008.LOG:
db2 "prune logfile prior to S000009.LOG" |
Note: Depending on your recovery strategy, there may be scenarios where previous roll forward operations are performed on the database. Old log files in your archive directory may be overwritten with newer log files with the same name, thereby preventing point in time recovery of a database using old log files. It is important for the programmer of the user exit program to take situations such as this into account.
In this article, we will be using the
db2uext2.cdisk
sample c program provided by DB2, which is located in your c directory. On Unix, the c directory is located at
<instance home>/sqllib/samples. On Windows, this directory is located in
Program Files/IBM/<instance name>/samples.
Setting up a user exit program on Windows
Modify and compile user exit program
1. Create a directory called
C:\mylogs
2. Copy the
C:\Program files\IBM\SQLLIB\samples\c\db2uext2.cdisk to a working directory.
3. For this example, the following part of the user exit program should be verified to reflect the path
c:\\mylogs\\.
#define ARCHIVE_PATH "c:\\mylogs\\" #define RETRIEVE_PATH "c:\\mylogs\\" #define AUDIT_ACTIVE 1 /* enable audit trail logging */ #define ERROR_ACTIVE 1 /* enable error trail logging */ #define AUDIT_ERROR_PATH "c:\\mylogs\\" /* path must end with a slash */ #define AUDIT_ERROR_ATTR "a" /* append to text file */ #define BUFFER_SIZE 32 /* # of 4K pages for output buffer */ |
4. Make sure a supported C compiler is installed on your system (i.e. Microsoft Visual Studio) and your environment has the compiler's path.
5. From the command line, rename the
db2uext2.cdisk
to
db2uext2.c
and build it:
cl db2uext2.c |
Once the program is compiled,
db2uext2.exe
and
db2uext2.obj
files will be created.
6. Place the
db2uext2.exe
executable in the
/SQLLIB/BIN
directory so that the database manager can find and execute it to archive and retrieve logs.
Create and prepare the database for user exit
7. Create the SAMPLE database with the db2sampl command at the DB2 command window. This will let you use the sample tables for the examples below.
db2sampl |
8. Update the database configuration file so that user exit is turned on for the database. Note that only one database can be assigned the user exit program since the bin directory is shared by all DB2 instances.
db2 "update db cfg for sample using userexit on" db2stop force db2start |
Note that the logretain database configuration parameter does not have to be on to enable roll forward recovery of log files since the userexit parameter also does this.
9. For testing purposes, you can encourage log files to be archived by decreasing the logfilsiz database configuration parameter.
db2 "update db cfg for sample using logfilsiz 200" |
The value 200 is the number of 4k pages for each log file. In our example, the size of each log file will be initialized to 800k (200 * 4k).
10. Since the userexit database configuration parameter is set to YES, a full database backup is required before a connection to the database can be made. Make a note of the timestamp produced from the backup command as this will be used during the database restore.
db2 "backup db sample to c:\backups" |
At this point, your user exit program is enabled and ready to be used. The user exit program will be invoked every 5 minutes to check the active log directory for log files that need to be archived. Note that the larger the log file specified with the logfilsiz database configuration parameter, the longer it will take to fill a log file thereby making that log file more vulnerable to disk failure, corruption, etc. Depending on your transaction loads, you should only specify a log file size that will fill up in a reasonable period of time so that the user exit program can archive the log file off to a safe directory.
Under a more ideal scenario it is best to archive the log files to a different disk on a separate system. This reduces I/O on the main disk and adds a second level of protection if the disk or system crashes.
Setting up a user exit program on AIX
Modify and compile user exit program
1. Create the following directory structure:
/mylogs/SAMPLE/NODE0000 |
Then give recursive permissions to each directory in the structure:
chmod -R 777 /mylogs
2. Copy the db2uext2.cdisk to a working directory and give the file permissions:
cp /home/db2v8_32/sqllib/samples/c/db2uext2.cdisk /home/db2v8_32/db2uext2.c chmod 777 /home/db2v8_32/db2uext2.c |
3. For this example, the following part of the user exit program should be updated to reflect the path
/mylogs/.
#define ARCHIVE_PATH "/mylogs/" /* path must end with a slash */ #define RETRIEVE_PATH "/mylogs/" /* path must end with a slash */ #define AUDIT_ACTIVE 1 /* enable audit trail logging */ #define ERROR_ACTIVE 1 /* enable error trail logging */ #define AUDIT_ERROR_PATH "/mylogs/" /* path must end with a slash */ #define AUDIT_ERROR_ATTR "a" /* append to text file */ #define BUFFER_SIZE 32 /* # of 4K pages for output buffer */ |
4. Make sure you have a C compiler installed on your system and your environment has the compiler's library and path in it.
5. From the command line, build the
db2uext2.c
program:
cc -o db2uext2 db2uext2.c |
Once the program is compiled, a db2uext2 executable will be created.
6. Move the db2uext2 executable to the
sqllib/adm
directory.
mv db2uext2 /home/db2v8_32/sqllib/adm/ |
Create and prepare the database for user exit
7. Create the SAMPLE database with the db2sampl command. This will allow you use the sample tables for the examples below.
db2sampl |
8. Update the database configuration file so that user exit is turned on for the database.
|
Note that the logretain database configuration parameter does not have to be on to enable roll forward recovery of log files since the userexit parameter also does this.
9. For testing purposes, you can encourage log files to be archived by decreasing the logfilsiz database configuration parameter.
db2 "update db cfg for sample using logfilsiz 200" |
The value 200 is the number of 4k pages for each log file specified by logprimary and logsecond parameter.
10. Since the userexit database configuration parameter is set to YES, a full database backup is required before a connection to the database can be made. Make a note of the timestamp produced from the backup command as this will be used during the database restore.
db2 "backup db sample to /home/db2v8_32/backups" |
At this point, your user exit program is enabled and ready to be used. The user exit program will be invoked every 5 minutes to check the active log directory for log files that need to be archived. Note that the larger the log file specified with the logfilsiz database configuration parameter, the longer it will take to fill a log file thereby making that log file more vulnerable to disk failure, corruption, etc. Depending on your transaction loads, you should only specify a log file size that will fill up in a reasonable period of time so that the user exit program can archive the log file off to a safe directory.
Under a more ideal scenario it is best to archive the log files to a different disk on a separate system. This reduces I/O on the main disk and adds a second level of protection if the disk or system crashes.
In the scenarios below, Windows is used to provide a visual representation for the location of specific files associated with the user exit program. After changing the paths, we also tested the steps below on AIX.
Note: For Windows users, upon first archival with the user exit program, the archive directory structure
\SAMPLE\NODE0000
will be created under the mylogs directory. The archive logs path on AIX, which is created manually, is similar with the following:
/sample/NODE0000
(see Setting up a user exit program on AIX).
Promote the Archival of log files
Scenario 1:
db2 connect to sample db2 "insert into staff (select * from staff)" db2 connect reset |
(all database connections must be released so that the database frees up resources and closes log files)
Check the
c:\mylogs\SAMPLE\NODE0000
directory.
The above scenario will truncate the currently active log file and copy it off to the c:\mylogs
directory. The size of a truncated log file will be less than 800k in order to prevent wasted space.
Figure 2 below shows the truncated log file that is moved to the archive directory.
Figure 2. Truncated log file
Scenario 2:
You can also test by filling your log file with data. The database manager will check for full log files and copy them off to the archive directory.
db2 "connect to sample" db2 "insert into staff (select * from staff)" |
(Perform this insert 10 times to fill the log file)
Remember that you updated the database configuration parameter logfilsiz to 200 4k pages (800k) in order to fill the log file quickly.
Now you can check the
c:\mylogs
directory.
Figure 3 below shows the placement of the archived logs.
Figure 3. Placement of archived logs
If your user exit program is working correctly, you should see an ARCHIVE.LOG created under c:\mylogs
and the one or more log files should be copied to
c:\mylogs\SAMPLE\NODE0000\.
Figure 4 below shows the placement of the
ARCHIVE.LOG
file:
Figure 4. Placement of ARCHIVE. LOG file
Retrieving the archived log files during database recovery
Now we will perform a database restore. The restore will invoke the user exit program to retrieve the log files from the c:\mylogs directory back into the active log directory where they can be applied to the database during roll forward recovery.
db2 connect reset db2 "restore database sample from vc:\backups taken at 20030416114420" SQL2539W Warning! Restoring to an existing database that is the same as the backup image database. The database files will be deleted. Do you want to continue? (y/n) |
The value 20030416114420 in the restore command is the timestamp taken from the previous database backup. Since we are restoring on top of our existing sample database, you will receive the above warning message. Select Y and press Enter.
db2 "rollforward db sample to end of logs and stop" Rollforward Status Input database alias = sample Number of nodes have returned status = 1 Node number = 0 Rollforward status = not pending Next log file to be read = Log files processed = S0000000.LOG - S0000004.LOG Last committed transaction = 2003-04-16-17.33.49.000000 DB20000I The ROLLFORWARD command completed successfully. |
Figure 5 below shows where the
RETRIEVE.LOG
is created.
Figure 5. Placement of ARCHIVE. LOG file
A message will be written into the
c:\mylogs\RETRIEVE.LOG
file for each log file passed back to the active log directory.
************************************************* Time Started: Wed Apr 16 12:47:31 2003 Parameter Count: 8 Parameters Passed: Database name:SAMPLE Logfile name: S0000004.LOG Logfile path:C:\SAMPLE\NODE0000\SQL00002\SQLOGDIR\ Node number:NODE0000 Operating system: NT Release: SQL08010 Request: RETRIEVE System Action: RETRIEVE to C:\SAMPLE\NODE0000\SQL00002\SQLOGDIR\ file S0000004.LOG from c:\mylogs\SAMPLE Media Type: disk User Exit RC: 0 Time Completed: Wed Apr 16 12:47:31 2003 |
I've presented a basic introduction to using user exit programs with DB2 UDB. It is important to note that a user exit program for DB2 UDB can be written and modified to suit your needs. Some additional programming considerations include deleting duplicate log files in the archive log directory that may be created under certain circumstances, allowing for the existence of different log files with the same name after a point-in-time recovery, removing log files from the archive directory that are no longer needed, and error handling. These are items for you to consider when modifying or creating your own user exit program. More information on user exit programming may be found in the links that follow.
David Kline works as a DB2 Technical Support representative for PartnerWorld for Developers. With the rest of his team, he helps Independent Software Vendors (ISVs) solve a wide range of development and administration issues. David has DB2 certifications in both application development and administration. You can reach David at djkline@us.ibm.com.
Table of contents
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
