DB2 10.5 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 that is 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 four ways to back up a database in a partitioned database environment:

Back up each database partition one at a time by 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.
Use a task assistant in IBM® Data Studio to guide you through the process of backing up the database.

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 must make one command call. However, when you use db2_all to back up a partitioned database, you sometimes still must make multiple calls to db2_all because the database partition that contains the catalog cannot 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 that were created using either of these methods is difficult because the time stamp for each database partition's backup image is 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 time stamp for each database partition backup. Alternatively, you can split your backup, specifying some database partitions for which you get the same time stamp, 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 must restore the catalog partition before you restore some or all of the other database partitions.

Procedure

To back up some or all of the database partitions of a partitioned database simultaneously by 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 acquires shared connections to the other database partitions, so user applications are able to connect to database partitions while they are being backed up.
On the database partition that contains the database catalog, perform the backup with appropriate parameters for partitioned databases, using one of the following methods:
- 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 that are 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.

For more information, see Including log files with a backup image.
Optional: Delete previous backup images. The method that 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 IBM 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 command to delete snapshot backup objects.