DB2 bpipe Backup with Community Bacula

The bpipe is a generic Community Bacula plugin that allows you to configure a backup script from almost any application by sending the data directly to Bacula through a Named Pipe (FIFO), without the need to save any data to the client machine.

We have a course on Udemy just about bpipe, with several examples, such as MySQL database backup, PostgreSQL, Firebird, LDAP; Virtual Machines Xen, KVM, Proxmox; Zimbra Mailboxes, inside others.

The bpipe is also present in Bacula Enterprise, but it already contains the plugins for more than 20 specific applications, with more automated features and more advanced techniques in reducing the size of these backups.

All bpipe backup and restore procedures should be tested before being deployed into production environments. Any feedback is appreciated.

Configuration

Enabling DB2 Incremental Backup

To specify whether the incremental backup is enabled for a database, you use the TRACKMOD configuration parameter. This parameter specifies whether the database manager will track database modifications so that the backup utility can detect which subsets of the database must be examined by an incremental backup and potentially included in the backup image.

The TRACKMOD configuration parameter can have one of the following two values:

NO— Incremental backup is not permitted. Database page updates are not tracked or recorded in any way. This is the default value.
YES— Incremental backup is permitted. When update tracking is enabled, the change becomes effective when the first successful connection to the database is made. Be aware that before an incremental backup can be taken on a particular tablespace, a full backup of that tablespace is necessary (more details on this follow the example below).

The following example shows how you would enable incremental backup:

DB2 UPDATE DATABASE CONFIGURATION FOR DBNAME USING TRACKMOD YES

It is possible to use something like the following script to enable the TRACKMOD equal to YES for all DB2 databases:

db2user=db2inst1
for dbname in $(su - $db2user -c "db2 list database directory" | grep Indirect -B 5 |grep "Database alias"| awk {'print $4'} |sort -u | uniq); do
su - $db2user -c "db2 UPDATE DATABASE CONFIGURATION FOR $dbname USING TRACKMOD YES"
done

If not configured yet, it is also necessary to enable the archive log mode for each database. E.g.

su - db2inst1 -c "db2 update db configuration for DBNAME using logarchmeth1 disk:/var/db2"

Now it is possible to perform differential (IBM calls it incremental) and incremental (delta backups).

bpipe Configuration

The following shell script is used to inform Bacula about what bpipe configurations are going to use for the backup job during execution.

It receives the backup Job level, fetches all DB2 existent databases, creates named pipes for each one and prints bpipe FileSet configurations to Bacula Job.

#!/bin/bash
#
# /opt/bacula/etc/db2_bacula.txt
#
# Script to generate Bacula FileSet bpipe syntax configuration in order to backup
# all db2 databases from an instance 
#
# Autorship: Heitor Faria (Copyleft: all rights reversed).
# Advisor: Julio Neves Shell Script Teacher - julio.neves@gmail.com
# Tested by: Heitor Faria
# 
# It must be called at the FileSet INCLUDE Sub-resource, used by the job that 
# backups DB2 machine and contains a Bacula Client, like this (e.g.):
#
# Plugin = "\\|/opt/bacula/etc/db2_bacula.txt %l"
#
# The following example shows how you would enable DB2 incremental backup and archive log, required by this script:
# 
# su - db2inst1 -c "db2 UPDATE DATABASE CONFIGURATION FOR DBNAME USING TRACKMOD YES"
# su - db2inst1 -c "db2 update db configuration for DBNAME using logarchmeth1 disk:/var/db2"
#

level=$1
db2user=db2inst1
pipedir=/mnt/pipe

# test if pipedir exists or create it
[[ -d "$pipedir" ]] || mkdir -p $pipedir

# verify backup level and set db2 backup command options
if [ $level == Differential ] 
then
options="INCREMENTAL"
fi

if [ $level == Incremental ] 
then
options="INCREMENTAL DELTA"
fi

# creates named pipes, calls db2 backup and configures Bacula bpipe
for dbname in $(su - $db2user -c "db2 list database directory" | grep Indirect -B 5 |grep "Database alias"| awk {'print $4'} |sort -u)
do
[ -p $pipedir/$dbname ] || {
mkfifo $pipedir/$dbname
chown $db2user $pipedir/$dbname
}
su - $db2user -c "db2 -r /tmp/db2bkp.log +o BACKUP DATABASE $dbname ONLINE $options TO $pipedir/$dbname WITHOUT PROMPTING" & 2>&- 
echo "bpipe:$pipedir/$dbname.db2.$level:cat $pipedir/$dbname && rm -f $pipedir/$dbname:dd of=/tmp/$dbname.db2.$level"
done

After saving a script to a folder (e.g.: /opt/bacula/etc/db2_bacula.txt), create a new Bacula FileSet with the following Include line:

Plugin = "\\|/opt/bacula/etc/db2_bacula.txt %l"

It is important to test all script commands in the shell. Run a test backup job and restore, and start the bacula-fd in debug mode if any errors appear.

If you want to restore the database backup directly to db2, replace the bpipe restore command with the following:

dd of=/tmp/$dbname.db2.$level && su - $db2user -c 'db2 restore db $dbname from /tmp/$dbname.db2.$level replace existing' && rm -f /tmp/$dbname.db2.$level