Who needs DB2's Incremental Delta Backup?

This
article explains how to organize and perform a delta incremental database
backup.

Delta backup will be your IT manager's best friend, especially
if he is fighting a budget, your DBA will love it because he will learn
something new, and storage guys will complain because they will not get paid as
before.

Delta
backup has been supported since version 7.2 (7.1 Fixpack 3) with a small
surprise:

Recovery history file
Special logging repository for tracking various database
activity: backup, restore, rollforward, alter, rename, quiesce tablespace,
load, drop, reorganization of table or update of table statistics. Entries from
this file are read with the LIST HISTORY command.

User exit for logging USEREXIT=ON
- The userexit parameter causes the database manager to call a user exit
program for archiving and retrieving database logs. This parameter allows
roll-forward recovery and automatically enables logretain to be active.

Incremental Delta Backup Example

We can choose different
storage mediums for saving a backup image. The most often used solutions are
local or remote disk file system, or TSM (Tivoli Storage Manager). TSM is an
enterprise-wide storage application for the network. It provides automated
storage management services (including backup and restore, archive and
retrieve, hierarchical space management and disaster recovery) to servers and
workstations connected to the network. TSM has two main components which are
important to delta backup: server and application program interfaces (API).

The TSM server is a
dedicated server machine with a dedicated backup storage pool (tapes or optical
drives).

TSM API code is a software
interface that provides the DB2 backup utility direct backup on the TSM server.

The TSM solution is
widely used for large databases. I will explain the TSM solution in detail and
give an example of hard disk usage.

Before we start the test,
let's check what needs to be installed and configured on the system: >

Tivoli Storage Manager client API

C compiler for compiling user exit program db2uext2.c

TSM management classes for full backup, delta backup and DB2 logs

(disk space on separate file system in case we make backup on hard disk)

Next we need to configure the user
exit program (db2uext2.c) to ensure that archived log files are correctly
handled and saved on TSM. Usually you only need to change the log destination
before compiling it.

The backup pending indicator (LOGRETAIN) now has the value
"BACKUP PENDING," which is the new recovery point for the database.
DB2 requires an offline backup to establish this new recovery point and get the
database out of the BACKUP PENDING state. Before making an offline backup we
have to close all connections and restart the database.

This information is
critical in making a final decision. In our case, delta backup is almost as
large as a full backup and will not be the right solution for us.

Conclusion

So do you really need
incremental/delta backup?

It depends in general
on the size of your database, how many changes you make per day, the speed of
you network, the maximum allowed downtime, LOBs... Incremental Delta backup
would have less meaning on very active databases because of the large number of
changed pages. However, if all changes are concentrated in a limited number of
the pages, incremental backup image will save a lot of time and storage.

More than that, DBAs
that support the database should understand the strategy and have a knowledge of
incremental delta backup.