DB2 Version 9.7 for Linux, UNIX, and Windows

Backing up partitioned databases

Backing up a database in a partitioned database environment can pose difficulties such as tracking the success of the backup of each database partition, managing the multiple log files and backup images, and ensuring the log files and backup images for all the database partitions span the minimum recovery time required to restore the database. Using a single system view (SSV) backup is the easiest way to back up a partitioned database.

About this task

There are three possible ways to back up a database in a partitioned database environment:

Back up each database partition one at a time using the BACKUP DATABASE command, the BACKUP DATABASE command with the ADMIN_CMD procedure, or the db2Backup API.
Use the db2_all command with the BACKUP DATABASE command to first back up the catalog partition and then to back up a specified list of database partitions.
Run a single system view (SSV) backup to back up some or all of the database partitions simultaneously, including the catalog partition.

Backing up each database partition one at a time is time-consuming and error-prone. Backing up all the partitions by using the db2_all command is easier than backing up each database partition individually because you generally only have to make one command call. However, when you use db2_all to back up a partitioned database, you sometimes still have to make multiple calls to db2_all because the database partition containing the catalog can not be backed up simultaneously with non-catalog database partitions. Whether you back up each database partition one at a time or use db2_all, managing backup images created using either of these methods is difficult because the timestamp for each database partition's backup image will be different, and coordinating the minimum recovery time across the database partitions' backup images is difficult as well.

For the previously mentioned reasons, the recommended way to back up a database in a partitioned database environment is to use an SSV backup because you can decide to back up all database partitions simultaneously, including the catalog partition, and get the same timestamp for each database partition backup. Alternatively, you can split your backup, specifying some database partitions for which you get the same timestamp, and later take additional backups on the other database partitions to complete the database backup. The catalog partition can be backed up at any time with any other database partitions.

Note: For restore operations, you still need to restore the catalog partition before restoring some or all of the other database partitions.

Procedure

To back up some or all of the database partitions of a partitioned database simultaneously using an SSV backup:

Optional: Allow the database to remain online, or take the database offline.
You can back up a partitioned database while the database is online or offline. If the database is online, the backup utility will acquire shared connections to the other database partitions, so user applications will be able to connect to database partitions while they are being backed up.
On the database partition that contains the database catalog, invoke backup with appropriate parameters for partitioned databases.
- Run the BACKUP DATABASE command with the ON DBPARTITIONNUMS parameter.
- Run the BACKUP DATABASE command with the ON DBPARTITIONNUMS parameter by using the ADMIN_CMD procedure.
- Call the db2Backup API with the iAllNodeFlag parameter.
- Open the task assistant for the BACKUP DATABASE command in IBM® Data Studio.
Optional: Include the log files required for recovery with the backup images.
By default, log files are included with backup images if you are performing an SSV backup (that is, if you specify the ON DBPARTITIONNUM parameter). If you do not want log files to be included with the backup images, use the EXCLUDE LOGS command parameter when you run the backup. Log files are excluded from the backup image by default for non-SSV backups.

See the following topic for more information: Including log files with a backup image.
Optional: Delete previous backup images. The method you use to delete old backup images depends on how you store the backup images. For example, if you store the backup images to disk, you can delete the files; if you store the backup images using Tivoli® storage manager, you can use the db2adutl utility to delete the backup images. If you are using DB2® Advanced Copy Services (ACS), you can use the db2acsutil to delete snapshot backup objects.