Archive and Backup with DB2 UDB for UNIX and Windows

Purpose

You must always have a recent and consistent copy of database data for DB2 UDB for UNIX and Windows that you can use to recover the database in the event of failure involving data loss.

Prerequisites

The database management system (DBMS) for DB2 UDB for UNIX and Windows supports the following types of backup:

Backup Types

Type of backup

What gets backed up

Complete online backup

Complete backup, all data are saved. The whole database is available for use during backup.

Incremental online backup

All changed data since the last complete backup are saved. The whole database is available for use during backup.

Delta online backup

All changed data since the last complete, incremental, or delta backup is saved. The whole database is available for use during backup.

Complete offline backup

Complete backup, all data is saved. The whole database is unavailable for use during backup.

Incremental offline backup

All changed data since the last complete backup is saved. The whole database is unavailable for use during backup.

Delta offline backup

All changed data since the last complete, incremental or delta backup is saved. The whole database is unavailable for use during backup.

The DB2 UDB for UNIX and Windows database management system (DBMS) supports the following granularity of backup:

Backup Granularity

Granularity of backup

What gets backed up

Database

Full backup, all data is saved

Table space

Partial backup, data of one or more table spaces is saved

Availability and performance considerations

The whole database is unavailable for use during offline backups. We recommend you to use online backups. However online backups might reduce overall database performance during backup. To reduce the time taken for a database backup you can choose incremental or delta backups and you can specify the degree of parallelism and the number of devices. Note that restoring an incremental or delta backup takes more time than restoring a full backup, because you have to first restore the previous complete backup.

Mirrored Backup

An alternative method for backups is the “suspend IO” functionality of DB2 UDB for UNIX and Windows. This enables you to rapidly split mirrored disks and then back up the split mirror.

Contact the ISICC IBM SAP International Competence Center in Walldorf, Germany for further information about the DB2 UDB for UNIX and Windows mirrored backup.

Process Flow

...

       1.      You decide which type of backup to do and what granularity to use:

Ў        Offline or online backups or both

Only offline backups cause system downtime. Use online backup if downtime cannot be tolerated or if your database is very large and would take too long to back up offline.

Ў        Complete, incremental, or delta backups

If you have decided that a complete backup takes too long, use incremental or delta backups. The backup time depends on how much data has been changed since the last complete, incremental, or delta backup.

Ў        Granularity of backup

If you have decided that a complete backup takes too long, use backups at the table space level. For example, if the database consists of 20 table spaces, back up four of them every night Monday to Friday (that is, equivalent to one complete backup a week). Then the worst case is that a table space damaged on Sunday would have to be recovered from last Monday’s backup.

       2.      You consider the frequency of backups, if possible increasing the frequency.

More frequent backups lead to shorter recovery time and therefore shorter downtime.  A good compromise is to make less frequent full backups, (for example, one full backup on Sunday) and more frequent table space backups (for example, back up one third of all table spaces Monday, Tuesday, Wednesday and again Thursday, Friday, Saturday). Then the worst case is to use a backup that is three days old.

       3.      You consider parallel backup to increase throughput.

DB2 offers parallel backup writers to reduce the time taken to back up the database. By choosing separate backup devices, the time required for a backup decreases substantially. For example a four-way server can handle four parallel backup sessions to four separate devices

       4.      You consider backing up to disk first if you have enough disk space.

A disk backup is usually faster than a tape backup because disk devices are generally faster. You can then copy your backups from disk to tape without incurring downtime. If possible, retain the disk backup copy since a restore from disk is faster than from tape. Note that this assumes that the disks are not mirrored.

       5.      You assess your tape devices.

Think about what kind of tape devices you are using for backing up your database since this determines the downtime in the case of offline backups. To give you some idea of this, the capacity of tapes currently ranges from 2 to 30 GB and the speed of data transfer ranges from 1 to 10 GB per hour.

       6.      You perform a backup.

You can perform backups with the Computing Center Management System (CCMS) or DB2 UDB for UNIX and Windows commands. Backups at file-system level using operating system commands are not supported.

       7.      You check your backups.

Check the existence and return code of your backups by using Computing Center Management System (CCMS). Additionally you can use db2ckbkp to test the integrity of a backup image and determine whether or not it can be restored.

For a detailed description of archive and backup procedures, refer to SAP Database Administration Guide: IBM DB2 Universal Database for UNIX and Windows or to the documentation for DB2 UDB for UNIX and Windows.

Result

You always have an up-to-date backup of your database. This lets you quickly recover the database in the event of a failure involving data loss. Therefore, the availability of your SAP system is increased because downtime due to the absence of a suitable database backup is minimized.