Setting up a DB2 Backup Plan

By on October 13th, 2020 in Best Practices, DB2, Technical

I recently was asked to set up a backup plan for some IBM DB2 databases that were moving from being backed up by TSM to being backed up by Veeam. Unlike TSM, Veeam does not have a like for like integration with DB2 (although there is for Oracle). This meant that the DB2 backup images would now need to be stored locally on disk before Veeam can back them up. Additionally, because the backups are performed online (i.e. you don’t need to stop the database) a large number of archive logs would also need to be stored locally.

This blog details the process I took to set this up.

BACKUP TYPES

The first thing to note is that there are 3 types of backup. A full backup takes an image of the whole database but there are 2 other types of backups that can be used to make the backups smaller if required.

Incremental. An incremental backup image is a copy of all database data that has changed since the most recent, successful, full backup operation.

Delta. A delta, or incremental delta, backup image is a copy of all database data that has changed since the last successful backup (full, incremental, or delta) of the tablespace in question.

You can use any combination of these 3 types but for me the decision came down to the following 3 factors:

  1. The size of a full backup
  2. Ease of restore/management
  3. Diskspace

SIZE OF BACKUP

The size of the backup is fairly closely related to the size of the database however DB2 provides a compress option. This reduces the image by about 80% however the time taken for the backup will also increase by up to 4 times. For large databases, this has a knock-on effect to the number of logs that are created during the backup and therefore the number of logs that are included in the backup. The DB2 backup “INCLUDE LOGS” option will contain the logs that are generated during the time that the backup is performed. The backup processing will include the First Active Log when the backup is started thru the log that is truncated at the end of the backup.

I tried the compress option initially but the slowness of it made it unusable for me but for smaller databases, it may well be worth using.

EASE OF RESTORE

I decided that as Veeam would backup the storage directory daily I would only need to keep 2 backups locally and because the customer was happy to add disk space and therefore for ease of management/restore I decided both of these backups should be full and include all the logs.

DISK SPACE REQUIREMENTS

My disk space requirement was for a backup area that was 3 times the size of a full backup. You may think it should be 2 however the way that DB2 prunes its backups (more of this later) means that it will only do this when the backup has successfully completed.  Therefore, if you set DB2 to keep 2 backups it will delete the oldest when the 3rd one is complete which means that until that happens you need space for 3. I added another 10% just to be certain.

Diskspace Required = (Number of backups to retain + 1) x Size of Full Backup  + 10%

For example, if my database size is 100GB and I want to keep 2 backups I would need 330 GB of local storage space.

In my case, I asked for a new filesystem to accommodate the new backups and ensure an existing filesystem would never be filled by a backup.

ARCHIVE LOGS

For online backups you must have archive logging turned on. This will also require an initial backup to be taken as archive logs are used in conjunction with the backups to perform a recovery. Without a valid backup, there is no point keeping archive logs.

When a transaction log is full and/or closed (e.g. end of a backup) Db2 will archive the log (assuming an archiving method is defined in LOGARCHMETH1). If you are storing these to disk you will need to have enough disk space to store all archive logs that are required to recover a database.  The disk space required is defined by the number of logs created after each backup until the next one occurs. For example, if you do a full backup each day at 2 am you will need to retain the archive logs created from the time of the backup until the next successful backup. If you want to retain 2 backups you will need to keep 2 sets of archive logs.

To turn on archive logging run these 2 commands changing SAMPLE to the name of your database.

$ db2 connect to SAMPLE
Database Connection Information
Database server        = DB2/LINUXX8664 10.1.3
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE
$ db2 update db cfg for SAMPLE using LOGARCHMETH1 DISK:/db2/archivelogs
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  One or more of the parameters submitted for immediate modification were not changed dynamically. For these configuration parameters, the database must be shutdown and reactivated before the configuration parameter changes become effective.

You will now need to restart the Database and do an initial backup.

MAINTENANCE OF BACKUPS AND ARCHIVE LOGS

Clearly, you don’t want to manage this process yourself. If you decide to keep 2 days of online backups we want this process to be automated. This is simple to do and DB2 provides 3 parameters that will set this up.

Parameter Value Description
NUM_DB_BACKUPS 2 Number of database backups to retain
AUTO_DEL_REC_OBJ ON Auto deletion of recovery objects    (backups and archive logs)
REC_HIS_RETENTN 0 This sets the maximum number of days to keep the backups.

If AUTO_DEL_REC_OBJ is set to ON, automated history file pruning and recovery object deletion are carried out based on the timestamp of the backup selected by the NUM_DB_BACKUPS database configuration parameter

The commands to set these parameters are:

$ db2 update database configuration for SAMPLE using NUM_DB_BACKUPS  2
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 update database configuration for SAMMPLE using REC_HIS_RETENTN 0
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

$ db2 update database configuration for SAMPLE using AUTO_DEL_REC_OBJ ON
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.

You will need to restart the database in order for these parameters to become active.

BACKUP COMMAND

The command that is used to backup the SAMPLE database is:

db2 backup db sample online to /db2/backups include logs

The DB2 backup INCLUDE LOGS option will contain the logs that are generated during the time that the backup is performed. The backup processing will include the First Active Log when the backup is started though to the log that is truncated at the end of the backup.

You can determine the “First Active Log” from the output of the DB2 cfg, for example:

db2 get db cfg for DBNAME |grep -i "first active log"  
First active log file = S0000198.LOG 

SCHEDULE

To schedule the backup I created a small script containing the backup command and then used cron to schedule it for 2am every day.

The backup script is called db2backup.sh and is a simple 2 lines

source /opt/IBM/db2/db2inst1/.bashrc
db2 backup db sample online to /db2/backups include logs

I then created a one-line file for the cron entry called sample_backup. This is created in /etc/cron.d and needs to be done as root.

This contains the following line:

00 02 * * * db2inst1 /home/db2inst1/scripts/db2backup.sh > /home/db2inst1/logs/db2backup.log 2>&1

This line instructs cron to run every day at 02:00 AM as the user db2inst1. You then need to restart cron to get it to re-read the new setting.

/etc/init.d/crond stop
Stopping crond:                                            [  OK  ]
/etc/init.d/crond start
Starting crond:                                            [  OK  ]

CHECKING THE BACKUP

Once you have scheduled it and it is running ok you can check the status of all the DB2 backups by running this command (changing SAMPLE for the name of the database).

db2 list history backup all for SAMPLE 

e.g.

My personal preference to check backups is to use a script that emails me any errors. The Perl script I use is part of a much larger offering that checks many other things (e.g. checks disk space, system performance, number of logs, sends alerts/emails) however I have supplied a shorter version which you can adapt to your requirements.  In the example, the script reads the SYSIBMADM.DB_HISTORY table to look at the database status. If you look at the script you will see I use quite a long SQL statement which does a lot of the work by converting status fields into meaningful text however you could so all this work in the Perl script too if you prefer.

my $stmt = "SELECT end_time, case(operationType) when 'F' then 'Offline Full' when 'N' then 'Online Full' when 'I' then 'Offline Incremental' when 'O' then 'Online Incremental' when 'D' then 'Offline Delta' when 'E' then 'Online Delta' else '?' end as Type, SQLCODE, case when (SQLCODE < 0 ) then 'ERROR' else 'OK' end as Status FROM SYSIBMADM.DB_HISTORY WHERE operation in ('B','R') ORDER BY start_time DESC FETCH FIRST 1 ROWS ONLY";

This sample script checks the database status, the status of the last backup, and the age of the last backup (in seconds). You can add alerts in too if you wish and you will notice my script errors if the age of the last database is older than 1 day (86400 seconds).

./chkdb2bu.pl
<db2-db-status>      [OK]    Connection to database SAMPLE on host falken01 was successful
<db2-backup-status>  [OK]    Last Online Full DB2 backup of SAMPLE had the status of OK
<db2-backup-date>    [OK]    Last DB2 database occurred at 20201007202337 (delta: 11555 seconds)

Download this script here.

If you have any questions then please let me know.

Visits: 5841