Dealing with DB2 UDB backup and archive log

Backing up your database is equally important as maintaining it.  One of the most important duty of a DBA is to ensure that the database is recoverable with zero to minimum data loss.  Why is it important to backup your database ?  Its an easy answer, recoverability. If you want to recover from a data corruption either caused by software or hardware or even worse a disaster.   You might have a well tuned application that can handle a million transactions per minute, but what good is it if the hard drive corrupts the database or one of your DBA accidentally drops a table or an earthquake swallows your datacenter and you cannot recover the data.  Imagine you telling your VP or CIO that you cannot restore the database and all the data is lost.  Ensuring that you have a recent, full backup of your database is an integral part of planning and implementing a disaster recovery strategy for your system. Last year I wrote a script that will warn you if a database backup doesn’t exsist.

DB2 UDB offers two major options : Offline backup and Online backup.
1. Offline backup :  Its a very comprehensive backup option.  An offline backup requires the database to be offline,  all connections to the database to be disconnected so that there is no inflight transactions.  This is the only way that you can restore without rollforward.  Here is a sample command for it :
BACKUP DB rajudb to /db2backup;

2. Online backup:  Unlike offline backup, online backup doesn’t require applications to be disconnected. Online backup is otherwise known as hot backups.  Applications can be selecting and updating the data while the backup is running.  You need to have archive logging turned on for executing an online backup.  In this case, you can restore the backup and use the archive log to rollforward for point in time (PIT) recovery.   There are three flavors of online backup :  full online backup,  incremental backups and delta backups.
Here is a sample command for online backup :
BACKUP DB rajudb to /db2backup online;

With online backup, you need to ensure that you save the corresponding logs, so that you can restore database and rollforward to a consistent point.  Since DB2 does log ahead , it will log all the changes before it externalize it to the table, you can the log to reach to a point in time during restore process.  Starting from DB2 V8.1, you have two options of turning on archive logging,  the traditional USEREXIT and LOGRETAIN combination and the LOGARCHMETH1. IBM recommends  using the LOGARCHMETH1 for turning on archive log. This does not require the traditional userexit to be configured. You can specify where the log needs to be archived and DB2 will automatically do that for you.
To turn archive log you simply issue :
UPDATE DB CFG USING LOGARCHMETH1 DISK: /db2inst1_archivelog1

You will have to take a backup  of database once you issue this command.

I am not going to explain the different types of online backup, you can find those explained really well on the IBM Information Center.

With archive log turned on and multiple online backups, one of the challenge a dba faces is cleaning up the old archive log and old backups.    On V8.x era,  I used the find and rm command to delete old archive log , backups and then prune the history file to keep it small.   Now with V9.7  there two  new ways to accomplish all of this : The PRUNE command and the automated PRUNE command.
You can now issue PRUNE HISTORY <timestamp> AND DELETE  command which will prune the history prior to the timestamp you specify and delete all the backup image and log files associated with them.  If you are not using the LOGARCHMETH1  and using LOGRETAIN, then you can use PRUNE LOGFILE PRIOR to <logfile name>.
Since most of us now use the LOGARCHMETH1, PRUNE HISTORY will be the main focus.  You can build the PRUNE command within your backup, so its a much cleaner approach :

PRUNE HISTORY 20101020 AND DELETE will delete all the entries from the history file prior to 20101020, the backup image files and archive log prior to 20101020. Optionally you can issue this through the ADMIN_CMD stored procedure as well:
CALL  SYSPROC.ADMIN_CMD(‘prune history 20101020 and delete’)

Now if you were like me and would like to automate simple administrative task, there is a configure and forget feature on V9.7 on the database configuration.  There are three parameters that can be set to automate the pruning task:

num_db_backups – specifies the number of database backups to retain for a database
rec_his_retentn - specifies the number of days that history will be retained
auto_del_rec_obj –  specifies whether the file should be deleted or not.

When auto_del_rec_obj is set to ON, and whenever there are more successful database backup entries than the num_db_backups configuration parameter, then the database manager will automatically prune  history file entries that are older than rec_his_retentn.

For example , if you have set num_db_backups to 7, rec_his_retentn to 7 and auto_del_rec_obj to ON.  Then once  there are more than 7 successful backups , db2 will prune recovery history data that is older than 7 days and will delete all the backup image and archive log that are associated with the deleted recovery history data.

These three setting eliminates the need for manually pruning the history file on a regular basis.  You can still issue the PRUNE HISTORY if you need to do for a adhoc requirement.

Here is a script that lets you backup the database and prunes history file :backupdb.sh . As always ensure that the script is safe to run on your environment and do let me know if you find a bug or have suggestions for improvement.