Archiving Transaction Logs to a Vendor Solution

I have worked with three different vendors for backup solutions for various clients in the last few months, and several others over the course of my career. When either making archive log decisions or evaluating vendors, it is important to know the DB2 details behind what is going on

Why Archive Transaction Logs at All?

First and foremost, archive logging must be enabled to take online backups. Even in some of the smaller data warehouses I support, it is unusual to make use of circular logging, even in non-production environments, much less production. I’ve seen a number of different recovery schemes and also a number of real-world failures, both easily handled and catastrophic. The firm I work for now is often called upon to help clients in what I like to think of as Extreme Recovery Scenarios – when people who are not trained DB2 DBAs have been supporting a database, and through questionable decisions have gotten themselves into a very painful recovery situation.

Archiving transaction logs is by far the norm over circular logging. The ONLY advantage circular logging has over archive logging is the ease in administering the transaction log files themselves, which is not that big of an advantage. Archiving transaction logs also enables the use of very handy tools like HADR.

The biggest advantage of archiving transaction logs is the ability to roll a database forward to any point in time for which you have appropriate backup images and transaction logs. For many of my clients this means that I can easily restore their database to any given second in the last two weeks. This type of recovery is less commonly needed when HADR is used, but I have still needed it in some of those extreme recovery scenarios.

If transaction logs are archived, they should be archived only to a separate directory, filesystem, or vendor device. They should not be allowed to accumulate in the active log directory. My policy on this comes from many times when a client has come to me who has accidentally deleted an active log file for a database, often because they don’t differentiate or know the difference between active and archived transaction logs. That is a painful recovery scenario most of the time

Why Externalize Archived Transaction Logs

To achieve your recovery goals, you must have the correct backup files and transaction logs. To protect against failures on the database server, backup files AND transaction logs should both be moved to an external solution as soon as possible after they are generated. In the cheapest solution, this can be simply copying the files to another server after they are generated. This ensures that if you lose the database server entirely and permanently, you still have your most precious resource – your data.

Where Externalized Archived Transaction Logs Can Go

One solution for externalizing archived transaction logs can be to choose a local disk as the location for archived transaction logs and then use OS or other processes to copy those files elsewhere on a regular basis. Without a vended enterprise backup solution, I find that clients are rarely willing to copy or back up the contents of this directory more often than daily, which can be inadequate for some failure scenarios and recovery goals.

TSM

I’ve worked with archiving logs to TSM since it was called ADSM and the only option was a compiled userexit. Today, the options on the DB2 side for this are easy, but as with all vendor solutions, you really need a talented engineer/admin for TSM or the vendor solution for it to be the least bit practical. When using TSM, you set LOGARCHMETH1 to TSM:mgmtclass_name, and have your TSM admin do any other required setup for the DB2 instance owning ID. Both backup images and transaction logs can easily be extracted using the db2adutl command. If you need to access the files for your primary database server on some other server, you have two choices. You can either work with your TSM admin to be able to directly access the files on TSM from the other server, or you can extract the files to disk on the primary server and then scp or use other means to move them to the other server.

After enabling TSM, always do basic restore and retrieval tests to make sure you can get things back from TSM when you need to.

Other Vendors

Using other vendors is similar in some respects. You can set LOGARCHMETH1 to VENDOR:/path/to/vendorLib/<library name>. However, you want to make sure this path is not too long, as that can cause problems. You also may need to specify options using the VENDOROPT DB CFG parameter. The values here should be supplied by the vendor in their documentation for DB2.

When you use another vendor, in addition to the basic restore/retrieve tests, you also will need to document the syntax and ability to do each of the following:

• List transaction logs
• List database backup images
• Retrieve backup image to disk
• Retreive transaction log files to disk

Surprisingly, not all vendors can handle the last two – at all. They may only support when DB2 retrieves the files via the restore or rollforward commands. To me, these are critical to have, as restoring is not always the only thing I want to do with these files – sometimes I need to move them to other locations or supply them to IBM support in extreme cases. If I had a say in choosing the backup solution, not allowing this would be grounds for elimination.

USEREXIT as a DB CFG Parameter was Deprecated; Use of a Userexit was Not

It is still possible to use userexit today. This is WRONG and it should not be used.

Userexit is a compiled executable written in C++ that defines what an archive of a transaction log does. When I started working with DB2 many years ago, it was the only way to get archived transaction logs to any vendor device, including TSM. It was a real pain, because you had to find the right C compiler to compile it after defining the paths or details, and you also had to save somewhere a copy of the un-compiled code so you could go back and figure out what it was doing. If you didn’t have that plain text file, then you had no way of knowing where archived transaction logs were really going. If you wanted to change where they were going, you had to recompile it.

This year, I ran into a client actually using a vendor-supplied userexit, and it had all those same problems. It took a couple of weeks for us to figure the whole thing out. The whole reason all of the LOGARCHMETH1 and related parameters were introduced was to avoid these issues. Don’t use userexit.

FAILARCHPATH

Whenever using TSM or other vendor solution, always set FAILARCHPATH. This gives you a safety margin if you connection to the vendor device fails. I like to set it to a separate monitored filesystem, and then if I get a space alert on that filesystem, I know there is a problem with my vendor archive logging. This is easier to catch than parsing the diag log (though I do that too).