datori

Database administration for fun and profit

Enabling automatic database backup in DB2 for LUW

DB2 for LUW since version 9.5 offers, among other autonomic features, automatic database backup. It is not enabled by default, and relying on automatic backup may not always be desirable, particularly in complex environments where the backup process needs to fit into a chain of other scheduled jobs, requires customization (for example, tablespace level backup), or relies on components outside DB2 control (e.g. split mirror backup).

However, in many cases automatic backup can help you reduce the routine work of scheduling it outside DB2 and managing space utilized by backup images and archived logs. It can work well in non-production environments, with small scale applications and departmental databases.

Here is how you can quickly set up automatic backup of a DB2 database.

1. Define the maintenance window and the backup policy.

<?xml version="1.0" encoding="UTF-8"?>
<DB2MaintenanceWindows 
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <!-- Online Maintenance Window  -->
 <OnlineWindow Occurrence="During" startTime="00:00:00" duration="24" >
  <DaysOfWeek>All</DaysOfWeek>
  <DaysOfMonth>All</DaysOfMonth>
  <MonthsOfYear>All</MonthsOfYear>
 </OnlineWindow>
</DB2MaintenanceWindows>

Both these items are defined by loading specially crafted XML files to the database (into the table SYSTOOLS.POLICY if you must know). Sample files with instructions are provided in the $INSTANCE_HOME/sqllib/samples/automaint directory; for our task we will need DB2MaintenanceWindowPolicySample.xml and DB2AutoBackupPolicySample.xml for the maintenance window definition and the backup policy respectively.

The simplest maintenance window definition might look like this:

<?xml version="1.0" encoding="UTF-8"?>
<DB2MaintenanceWindows 
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <!-- Online Maintenance Window  -->
 <OnlineWindow Occurrence="During" startTime="00:00:00" duration="24" >
  <DaysOfWeek>All</DaysOfWeek>
  <DaysOfMonth>All</DaysOfMonth>
  <MonthsOfYear>All</MonthsOfYear>
 </OnlineWindow>
</DB2MaintenanceWindows>

It tells DB2 that only online maintenance tasks can be scheduled at any time of day, on any day. This is, by the way, the default maintenance window definition that is automatically created along with the database.

You can limit the time range when online tasks might be executed by specifying the window start time (using 24-hour clock) and duration (in hours). The following, for example, will enable a 6-hour window starting at 23:00 hours (that is, 11pm):

<OnlineWindow Occurrence="During" startTime="23:00:00" duration="6" >

If Occurrence is set to "Outside", it reverses the meaning of start time and duration; the following will enable online tasks at any time except between 11pm and 5am:

<OnlineWindow Occurrence="Outside" startTime="23:00:00" duration="6" >

You can further refine the window by specifying days of the week:

<DaysOfWeek>Sat Wed</DaysOfWeek>

days of the month:

<DaysOfMonth>1 11 21</DaysOfMonth>

and months:

<MonthsOfYear>Apr Nov</MonthsOfYear>

Note that days and months specifications combine with the logical “AND”. If the three examples above are defined simultaneously, the maintenance tasks will be scheduled on Saturdays and Wednesdays only when these days fall on the 1st, 11th, or 21st days of April or November.

If you plan on doing offline backups, you need to include a similarly specified <OfflineWindow> element in the file.

Save the file in the directory $INSTANCE_HOME/sqllib/tmp and load it into the database by calling the AUTOMAINT_SET_POLICYFILE system stored procedure:

db2 connect to mydb
db2 "call automaint_set_policyfile('MAINTENANCE_WINDOW', 'windowdef.xml')"

Now we can define the backup policy. The example below defines an online backup that will save images to /db2data/backup and will be triggered when all three conditions apply:

  • There are fewer than 2 existing backup images.
  • One week (168 hours) has passed since the time of the previous backup.
  • There have been more than 25 MB (6400 x 4KiB) of logs generated since the time of the previous backup.

Example:

<DB2AutoBackupPolicy 
xmlns="http://www.ibm.com/xmlns/prod/db2/autonomic/config" >
 <!--   Backup Options  -->
 <BackupOptions mode="Online">
  <BackupTarget>
  <DiskBackupTarget>
    <PathName>
      /db2data/backup
    </PathName>
  </DiskBackupTarget>
  </BackupTarget>
 </BackupOptions>
 <!--  Frequency of automatic backups -->
 <BackupCriteria numberOfFullBackups="2" timeSinceLastBackup="168" logSpaceConsumedSinceLastBackup="6400"/>
</DB2AutoBackupPolicy>

The sample policy file DB2AutoBackupPolicySample.xml provides examples of other backup target types.

Save the file, as before, in $INSTANCE_HOME/sqllib/tmp, and load it into the database:

db2 "call automaint_set_policyfile('AUTO_BACKUP', 'bkp_policy.xml')"

2. Set database configuration parameters

There are several database configuration parameters that affect automatic backup.

AUTO_MAINT

This is the “master switch” of all automatic maintenance tasks. It must be set to ON, which is the default value, for the automatic backup to work.

AUTO_DB_BACKUP

This parameter, when set to ON enables automatic backup.

NUM_DB_BACKUPS

This parameter determines how many older backup images must exist before the corresponding records can expire from the database history file. Expired history records will be removed from the history file during the next backup operation.

REC_HIS_RETENTN

This parameter defines how many days to keep the database history records before they can expire. If you require a certain number of older backup images to be kept, make sure that REC_HIST_RETENTN allows this, given the backup frequency.

If REC_HIST_RETENTN is set to -1 or 0, only NUM_DB_BACKUPS determines when database history records expire.

AUTO_DEL_REC_OBJ

This parameter, when set to ON, causes older backup images and archived log files to be deleted from disk when the corresponding expired records are purged from the database history file. This helps manage disk utilization by the recovery objects in accordance with the backup expiration policy.

You can set all the required parameters in one step:

db2 update db cfg for mydb using auto_db_backup on num_db_backups 5 \
                                 rec_his_retentn 60 auto_del_rec_obj on

The database must be deactivated and reactivated for the changes to take effect.

Leave a Reply

Your email address will not be published. Required fields are marked *