Using Vendor Backup Solutions with DB2 for LUW

There are an astonishing number of vendor solutions available with specific interfaces for DB2. Working with a variety of clients, I see and help to evaluate and implement a variety of backup solutions. I thought I’d share some of the things I look for and work on as part of an implementation. Sometimes the DBA has input on a solution chosen, and other times, A solution is dictated and a DBA must simply implement it.

Backup Solution Expertise

With any solution, engaging an expert in that solution is key. Whether that expert is someone who is hired to implement the solution and train one or more internal resources on it, or whether someone is hired with that expertise, this is critical. A backup solution is not a “set it and forget it” kind of thing. Implementation is time consuming, testing is time consuming, and over time, issues occur that require expertise. If you don’t have someone permanently on staff with the expertise, then hire a qualified consultant and require that they also produce detailed documentation that not only your regular staff can use for routine work, but that another expert would understand in case you have to bring in someone else in the future.

Functions a Backup Solution Should Provide

Each time I am asked to evaluate a solution, I look at the details of how to perform these tasks. If I am asked to participate in the implementation of a solution, I will insist on testing each of these areas.

Backups/Restores of Databases

It may be obvious, but sometimes the syntax for a backup and/or restore may be a bit different. It is crucial to not only test the backup, but also the restore. On both, compare duration to backup to disk or to the former backup solution. I’ve seen a backup solution used that increased backup time by a factor of 10 due to improper network speed with the new solution. While this may be an annoyance on backup, it will likely completely destroy your RTO on restore. I also like to test a restore, a restore with rollforward (prefferably after some time has passed), a redirected restore, and a restore to a different server.

Archiving of Transaction Logs

Most solutions for database backups will provide the ability to archive transaction logs directly to the backup solution. After interrogating the expert about how many copies of each archive log are kept in case of corruption or other problem, I’ll look into the details of how they recommend setting things up to accomplish this. I have actually within the last year seen a vendor that required a use of a user exit. This is a red flag to me, as a user exit is an older way of doing things, and can be problematic to deal with. Archiving of transaction logs should be able to be handled with a vendor library and a few settings at the database level. No need to have the black box of a user exit getting in the way.

Listing Backup Images and Transaction Log Files Available

If using TSM, this is easy using the db2adutl command. However, each vendor should have a similar command (though likely not with “db2” in the title) that the DBA must be able to run to see what backup images are available to restore. This is critical to test. I usually have to work through three things to make this work:

• The syntax of the vendor command
• Ensuring the instance owner and SYSADM groups have the right permissions to run the command
• Details of the input to the vendor’s command

Extracting Backup Images to Disk

Being able to backup and restore an image is one thing, but you should also be able to extract that image to disk. Why? Maybe you need to transfer the image to a server that will not have access to the vendor solution. Maybe you need to supply a backup image to IBM or an application vendor’s support personnel to work through a problem. It is not enough to just be able to run backups and restores.

Extracting Transaction Log Files to Disk

For the same reasons you need to be able to access backup images, you also need to be able to access transaction log files. Many restore scenarios require at least a few transaction logs.

Functions a Backup Solution May Provide

In addition to the minimum functionality, a backup solution may provide other functionality.

Accessing Backup Files and Transaction Logs from a Different Server

As a part of my restore testing, I always try to test a restore on a different server than where the backup was taken. If this requires extracting the backup image to disk and then transferring the image, that is generally fine with me. If there is a way to access the backup image directly on the vendor’s backup solution from another server, that’s fine as well. Direct access can save time, especially since the connection to the backup solution may be faster than the connection available to transfer a file.

Deduplication

Most vendor backup solutions provide some sort of deduplication. If you’re using such a solution make sure that you do not compress or encyrpt your backup images as either will nullify the benefits of deduplication. This may mean that you need to have enough disk space on your database server to hold an uncompressed backup image, in case you need to extract one to disk.

Flash Copy at the Storage Level

Some vendor backup solutions may suspend writes on the database and take a flash copy at the storage level. This can work well with DB2, as long as the backup procedure includes suspending writes and your database filesystems are appropriately segregated by database to make independent restores possible. Did you know that you can even roll forward through transaction logs after bringing up a backup taken in this manner? Just ensure that you perform the same kinds of restore tests and are aware of the restrictions with this methodology.

Pruning Backup Images and Transaction Log Files

DB2 provides methods for deleting old backups and transaction log files. Make sure you communicate with the expert in the vendor backup solution to see who is responsible for pruning old images in this scenario. If the vendor solution performs this work, verify that the numbers they are specifying for each match what you need to meet requirements at the databases level.

DB2 Settings for Vendor Solutions

Often, using a vendor solution for DB2 backups, restores, and transaction log file archiving requires setting three DB2 parameters – VENDOROPT, LOGARCHMETH1, and LOGARCHOPT1.

Vendor Options

The VENDOROPT database configuration parameter will often specify a configuration file that DB2 will need to know about for backup, restore, or load copy operations. It can specify other details needed by the vendor solution.

Log Archive Method and Options

The LOGARCHMETH1 database configuration parameter will be set to VENDOR: followed by a string that will likely specify the vendor library. The LOGARCHOPT1 database configuration parameter will likely specify the name of the configuration file (which may be the same as specified in VENDOROPT), and may specify other details that the vendor solution needs.

TSM

TSM continues to have its own set of parameters that are specified directly in the database configuration. These parameters include:

• TSM_MGMTCLASS
• TSM_NODENAME
• TSM_OWNER
• TSM_PASSWORD

For other vendor solutions, these are things that are often specified in one of the vendor option locations or the vendor configuration file. IBM just makes it easier to use their products together. Like with any other vendor solution, you need a TSM expert to help get TSM set up in a stable and secure way. TSM also has a dedicated db2 command – db2adutl – to list and extract backup images and transaction logs.

Backup Command

The backup command often looks a bit different when using a vendor solution. Here’s one example from a client that uses a product called ‘NetWorker’:

db2 backup db db_name online load /usr/lib/libnsrdb2.so options @/nsr/apps/config/db_name_db2.cfg dedup_device include logs

This is the same library that we specify in LOGARCHMETH1 and the same config file that we specify in VENDOROPT and LOGARCHOPT1 parameters. Notice also that we do not encrypt or compress the backup image and we also specify an option – DEDUP_DEVICE in the backup command that optimizes the backup image for deduplication. For TSM, the location is specified as USE TSM instead of LOAD <library>

Parallelism

With many vendor solutions, you can specify that multiple sessions be opened to the vendor product if bandwidth is available. This can speed up the backup, so you may want to try this. This is specified with the OPEN <num-sessions> SESSIONS syntax.

Warning Signs in Vendor Backup Solutions

I have seen bad vendor solutions or perhaps bad implementations of vendor solutions. Look out for vendor solutions that don’t have details around DB2 – they may not be aware of the intracacies of DB2 backups. Also keep an eye on CPU and I/O stats before and after implementation of a vendor solution. I have seen a vendor solution crash a running DB2 production database server (one of the rare cases I’ve seen an error condition require a full server reboot to recover from on AIX). So be sure that you or someone is thoroughly analyzing things before you get to production.