Scheduling an LDAS Database Backup

To facilitate nightly backups of the LDAS DB2 databases, we created a Perl script backup_db2_SERVER_NAME.pl. This document explains how to configure the script for any DB2 server and uses the MetaServer configuration as an example.

Step 1: Configure the databases for ONLINE backups

The ONLINE parameter of DB2's backup command tells DB2 to allow users to have full access to a database while the backup is running. If the ONLINE parameter is not included in the command, users are locked out until the backup completes. To enable ONLINE backups, the LOGRETAIN and USEREXIT configuration settings must be set to RECOVERY and ON. By default, both settings are set to NO to limit the amount of disk space needed for transaction logs. You need to change these settings for all of the databases you want to backup with this script.
  1. Log in as the database instance user. For Metaserver, log in as ldasdb.
  2. Force all users out of all of the databases by issuing the following command:
    db2 force application ALL
  3. Run the following command to configure the database to retain the transaction logs:
    db2 update db cfg for [DATABASE NAME] using logretain recovery
  4. Run the following command to configure the database for user log management:
    db2 update db cfg for [DATABASE NAME] using userexit on
  5. Backup up the database without ONLINE. After the backup, the server will begin retaining the transaction logs and will be ready for ONLINE backups..
    db2 backup database [DATABASE NAME] to /usr2/backup with 2 buffers buffer 1024

Step 2: Configure the Script for the Server

The script is designed to work on any DB2/Linux server. You just have to download it, change some constants and environment variables at the top of the script and then run it. The original version of the script has eight constants needed by the script and 5 environment variables that are needed by DB2. The constants tell the script which databases to backup, where to put the backup files, how long to keep old backup files, who to notify when the script is finished, etc. Comments within the script provide information about each of the constants. The script also explicitly sets several environment variables so that the DB2 commands will execute properly under cron. Jobs running under cron have a limited set of environment variables.
  1. Click here to download the script. Save it into the home directory of the DB2 instance owner (or any other appropriate directory).
  2. Open the script in a text editor.
  3. Find the section of the script labelled CONFIGURATION CONSTANTS at the beginning of the script.
  4. Set each of the constants and environment variables for your environment.
  5. Save the script with the name of your server instead of SERVER_NAME. The script name contains the server name to show that it has been configure for a particular server. For example, the MetaServer script is named backup_db2_METASERVER.pl.
  6. Run the script to make sure that it is properly configured and to backup the databases.
    perl -w backup_db2_SERVER_NAME.pl

Step 3: Add the Script to Cron

To make the database backups routine, you need to schedule them. Cron is a job scheduling software package that is standard on most flavors of UNIX. Scheduled jobs are listed in the crontab files. Once a minute cron looks through the crontab files and runs any jobs that are scheduled for that minute. This script is designed and tested to work with cron.
  1. Log in as the database instance user. For Metaserver, log in as LDASDB.
  2. To display the crontab file for the user in a text editor, enter the following command:
    crontab -e
  3. The crontab file is composed of comments lines and job lines. You should enter a comment above each job to explain what it does. The job line is composed of 6 columns. The first 5 columns, which specify when the job should run, stand for minute, hour, day, week, month. Enter these values as numbers. If you want cron to run a job every minute, hour, day, week, or month, put an * instead of a number in the respective column. You can enter multiple values for a column by seperating them with a comma. For more information refer to the man pages for cron and crontab. Here's an example that schedules the backup of Metaserver for everynight at 1:15 AM: 
    # min hour day week month job
# Schedule the LDAS DB Backup
15 1 * * *      /usr2/ldasdb/backup_db2_METASERVER.pl
  4. Save your changes.

For more information, see the following:

  1. Performing a Database Backup, Restore, and Rollforward
  2. IBM DB2 Command Reference
  3. Quick Beginnings for DB2 Extended Enterprise Edition for UNIX

$Id: scheduling_ldas_DB_backup.html,v 1.20 2003/11/21 16:32:50 dsmackin Exp $