Backup Overview | Advanced DBA Certification Guide and Reference for DB2 Universal Database v8 for Linux, UNIX, and Windows

The simplest form of the BACKUP DATABASE command requires only that you specify the alias or name of the database that you want to back up.

For example:

  db2 backup db sample

If the command completes successfully, you will have acquired a new backup image that is located in the path or the directory from which the command was issued. It is located in this directory because the command in this example does not explicitly specify a target location for the backup image.

On the Windows operating systems, for example, this command (when issued from the root directory of the D: drive) creates an image that appears in a directory listing as follows :

  Windows:   Directory of D:\SAMPLE.0\DB2\NODE0000\CATN0000020829   08/29/2002 15:26p <DIR> .   08/29/2002 15:26p <DIR> ..   08/29/2002 15:26p 12,615,680 122644.001

On UNIX, the backup image name will be of the form below:

  UNIX:   SAMPLE.0.v8inst.NODE0000.CATN0000.20020829152626.001

NOTE

If the DB2 client and server are not located on the same system, the default target directory for the backup image is the current working directory on the client system where the command was issued, unless a path for the backup image is specified. This target directory or device must exist on the server system.

You have the option to specify the backup target location when you invoke the backup utility. This backup target location can be:

A directory (for backups to disk or diskette)
A device (for backups to tape)
A Tivoli Storage Manager (TSM) server
Another vendor's server, such as VERITAS, etc....

The recovery history file is updated automatically with summary information whenever you invoke a database backup operation. This file is created in the same directory as the database configuration file.

On UNIX-based systems, file names for backup images created on disk consist of a concatenation of several elements, separated by periods:

  DB_alias.Type.Inst_name.NODE    nnnn    .CATN    nnnn    .timestamp.Seq_num

On Windows operating systems, a five-level subdirectory tree is used:

[View full width]

[View full width]
DB_alias.Type\Inst_name\NODE nnnn \CATN nnnn \yyyymmdd\hhmmss.Seq_num For example: D:\SAMPLE.0\DB2\NODE0000\CATN00000208292644.001 DB_alias: A 1- to 8-character database alias name that was specified when the backup utility was invoked. Type: Type of backup operation, where: 0 represents a full database-level backup 3 represents a table space level backup 4 represents a backup image generated by the LOAD...COPY TO command. Inst_ name: A 1- to 8-character name of the current instance that is taken from the DB2INSTANCE environment variable. NODE nnnn : The database partition number. In non-partitioned database systems, this is always NODE0000. In partitioned database systems, it is NODE xxxx , where xxxx is the number assigned to the database partition in the db2nodes.cfg file. CATN nnnn : The database partition number of the catalog partition for the database. In non-partitioned database systems, this is always CATN0000. In partitioned database systems , it is CATN nnnn , where nnnn is the number assigned to the database partition in the db2nodes.cfg file. Date: An 8-character representation of the date at which the backup operation was performed. The date is in the form yyyymmdd , where: yyyy represents the year (1995 to 9999) mm represents the month (01 to 12) dd represents the day of the month (01 to 31) Time: A 6-character representation of the time at which the backup operation was performed . The time is in the form hhmmss, where: hh represents the hour (00 to 23) mm represents the minutes (00 to 59) ss represents the seconds (00 to 59) Seq_num: is a 3-digit number used as a file extension.

When a backup image is written to tape:

File names are not created, but the information described above is stored in the backup header for verification purposes.
A tape device must be available through the standard operating system interface. On a large partitioned database system, however, it may not be practical to have a tape device dedicated to each database partition. You can connect the tape devices to one or more TSM servers, so that access to these tape devices is provided to each database partition.
On a partitioned database system, you can also use products that provide virtual tape device functions, such as CLIO/S. You can use these products to access the tape device connected to other database partitions through a pseudo tape device. Access to the remote tape device is provided transparently , and the pseudo tape device can be accessed through the standard operating system interface.

You cannot back up a database that is in an inconsistent state. If any table space is in an abnormal state, you cannot back up the database or that table space, unless it is in backup pending state.

If a database or a table space is in a partially restored state because a system crash occurred during the restore operation, you must complete the restore of the database or the table space before you can back it up.

A table space level backup operation will fail if a list of the table spaces to be backed up contains the name of a temporary table space.

If you are using the backup utility for concurrent backup operations to tape, ensure that the processes do not target the same tape.

Displaying Backup Information

You can use db2ckbkp to display information about existing backup images.

This utility allows you to:

Test the integrity of a backup image and determine whether it can be restored.
Display information that is stored in the backup header.
Display information about the objects and the log file header in the backup image.

Authorities Required to Use Backup

Privileges enable users to create or access database resources. Authority levels provide a method of grouping privileges and higher level database manager maintenance and utility operations. Together, these act to control access to the database manager and its database objects. Users can access only those objects for which they have the appropriate authorization, i.e., the required privilege or authority. You must have SYSADM, SYSCTRL, or SYSMAINT authority to use the backup utility.

Using Backup

You should not be connected to the database that is to be backed up. The backup utility automatically establishes a connection to the specified database, and this connection is terminated at the completion of the backup operation. Offline backup gets an exclusive connection to the database.

The database can be local or remote. The backup image remains on the database server, unless you are using a storage management product, such as TSM.

On a partitioned database system, database partitions are backed up individually. The operation is local to the database partition on which you invoke the utility. You can, however, issue db2_all from one of the database partitions in the instance to invoke the backup utility on a list of servers (or all servers), which you identify by database partition number. (Use the LIST DBPARTITIONNUMS command to identify the database partitions that have user tables on them.) If you do this, you must back up the catalog partition first, then back up the other database partitions.

NOTE

You should also keep a copy of the db2nodes.cfg file with any backup copies you take as protection against possible damage to this file.

On a distributed request system, backup operations apply to the distributed request database and the metadata stored in the database catalog (wrappers, servers, nicknames, and so on). Data source objects (tables and views) are not backed up, unless they are stored in the distributed request database.

If a database was created with a previous release of the database manager, and the database has not been migrated , you must migrate the database before you can back it up.

The following restrictions apply to the backup utility:

A table space backup operation and a table space restore operation cannot be run at the same time, even if different table spaces are involved.
If you want to be able to do rollforward recovery in a partitioned database environment, you must regularly back up the database on the list of database partitions, and you must have at least one backup image of the rest of the database partitions in the system (even those that do not contain user data for that database).

Two situations require the backed-up image of a database partition at a database partition that does not contain user data for the database:

You added a database partition to the database system after taking the last backup, and you need to do forward recovery on this database partition.
Point-in-time recovery is used, which requires that all database partitions in the system are in rollforward pending state.

The backup utility can be invoked through the command line processor (CLP), the database Backup Wizard in the Control Center, or the db2Backup API.

Following is an example of the BACKUP DATABASE command issued through the CLP:

  db2 backup database sample to c:\DB2Backups

To open the database Backup Wizard:

From the Control Center, expand the object tree until you find the Databases folder.
Click on the Databases folder. Any existing databases are displayed in the pane on the right side of the window.
Click the right mouse button on the database you want in the contents pane, and select Backup Wizard from the pop-up menu. The database Backup Wizard opens.

NOTE

Detailed information is provided through the online help facility within the Control Center.

Backing Up to Tape

When you back up your database or table space to tape, you must correctly set your block size and your buffer size. This is particularly true if you are using a variable block size (on AIX, for example, if the block size has been set to zero).

There is a restriction on the number of fixed block sizes that can be used when backing up. This restriction exists because DB2 writes out the backup image header as a 4-KB block. The only fixed block sizes DB2 supports are 512, 1024, 2048, and 4096 bytes. If you are using a fixed block size, you can specify any backup buffer size. However, you may find that your backup operation will not complete successfully if the fixed block size is not one of the sizes that DB2 supports.

An example of backing up and restoring the database to and from an attached tape drive:

  db2 backup database sample to /dev/rmt0 with 2 buffers buffer 512   db2 restore database sample from /dev/rmt0 taken at <timestamp>   with 2 buffers buffer 512

Backing Up to Named Pipes

DB2 supports database backup to (and database restore from) local named pipes on UNIX-based systems. Both the writer and the reader of the named pipe must be on the same machine. The pipe must exist and be located on a local file system. Because the named pipe is treated as a local device, there is no need to specify that the target is a named pipe.

Following is an example:

Create a named pipe:

  cd /data/dbbackup/   mkfifo mypipe   ls -altr mypipe   prw-r--r--   1 v8inst   sys            0 Aug 29 01:09 mypipe

If this backup image is going to be used by the restore utility, the restore operation must be invoked before the backup operation, so that it will not miss any data:
```
  db2 restore database sample from /data/dbbackup/mypipe into newdb &  
```
Use this pipe as the target for a database backup operation:
```
  db2 backup db sample to /data/dbbackup/mypipe  
```

Backup Database: Examples

In the following example, the database SAMPLE is defined on all four partitions, numbered 0 through 3. The path /data/dbbackup is accessible from all partitions. Partition 0 is the catalog partition and needs to be backed up separately because this is an offline backup. To perform an offline backup of all the SAMPLE database partitions to /data/dbbackup, issue the following commands from one of the database partitions:

  db2_all '<<+0< db2 backup db sample to /data/dbbackup'   Backup successful. The timestamp for this backup image is : 20020829012853   phantom: db2 backup database ... completed ok   db2_all '<<-0< db2 backup db sample to /data/dbbackup'   rah: omitting logical node 0   Backup successful. The timestamp for this backup image is : 20020829012956   phantom: db2 backup database ... completed ok   Backup successful. The timestamp for this backup image is : 20020829013012   phantom: db2 backup database ... completed ok   Backup successful. The timestamp for this backup image is : 20020829013028   phantom: db2 backup database ... completed ok   ls /data/dbbackup/   SAMPLE.0.v8inst.NODE0000.CATN0000.20020829012853.001   SAMPLE.0.v8inst.NODE0001.CATN0000.20020829012956.001   SAMPLE.0.v8inst.NODE0002.CATN0000.20020829013012.001   SAMPLE.0.v8inst.NODE0003.CATN0000.20020829013028.001

In the second command, the db2_all utility will issue the same backup command to each database partition in turn (except partition 0). All four database partition backup images will be stored in the /data/dbbackup directory.

In the following example, database SAMPLE is backed up to a TSM server using two concurrent TSM client sessions. The backup utility will use four buffers, which are of the default buffer size (1024 x 4-KB pages).

  db2 backup database sample use tsm open 2 sessions with 4 buffers   or   db2 backup database sample use tsm open 2 sessions with 4 buffers buffer 1024

If the util_heap_sz < ((number of buffers x buffer size) x number of data partitions), then an error (SQL2009C) will occur. The SQL2009C error indicates that there is not enough memory available to run the utility. You must increase the utility heap size (util_heap_sz).

  db2 backup database sample with 4 buffers buffer 4096   SQL2009C  There is not enough memory available to run the utility    .

Following is a sample weekly incremental backup strategy for a recoverable database. It includes a weekly full database backup operation, a daily non-cumulative (delta) backup operation, and a mid-week cumulative (incremental) backup operation:

  Day of week       Backup Operation   Sunday            Full   Monday            Online incremental delta   Tuesday           Online incremental delta   Wednesday         Online incremental   Thursday          Online incremental delta   Friday            Online incremental delta   Saturday          Online incremental   db2 backup db sample use tsm   db2 backup db sample online incremental delta use tsm   db2 backup db sample online incremental delta use tsm   db2 backup db sample online incremental use tsm   db2 backup db sample online incremental delta use tsm   db2 backup db sample online incremental delta use tsm   db2 backup db sample online incremental use tsm

The following command failed because an online backup allows other transactions to be changing the database simultaneously . Therefore, only a database configured for rollforward recovery can support an online backup.

  User A connects to the database and selects a table   db2 connect to sample   db2 "select count(*) from employee"   This database is configured as circular logging, and you want to take an online backup   db2_all "db2 backup db sample online to /data/backup"    the utility did not complete

Now, you want to take an offline backup, and the utility still failed because an offline backup requires exclusive use of the database. User A is already connected to the database earlier. To resolve this problem and perform the backup again, you must do the following.

  Now you want to perform an offline backup   db2_all "db2 backup db sample to /data/backup"    the utility did not complete   To resolve, you must do   db2 terminate   db2 force application all   db2_all "db2 backup db sample to /data/backup"    the utility complete successfully

Optimizing Backup Performance

To reduce the amount of time required to complete a backup operation:

Specify table space backup. You can back up (and subsequently recover) part of a database by using the TABLESPACE option on the BACKUP DATABASE command. This facilitates the management of table data, indexes, and long field or LOB data in separate table spaces.
Increase the value of the PARALLELISM parameter on the BACKUP DATABASE command so that it reflects the number of table spaces being backed up. The PARALLELISM parameter defines the number of processes or threads that are started when reading data from the database. Each process or thread is assigned to a specific table space. When it finishes backing up this table space, it requests another. Note, however, that each process or thread requires both memory and CPU overhead: On a heavily loaded system, keep the PARALLELISM parameter at its default value of 1.
Increase the backup buffer size. The ideal backup buffer size is a multiple of the table space extent size. If you have multiple table spaces with different extent sizes, specify a value that is a multiple of the largest extent size.
Increase the number of buffers. If you use multiple buffers and I/O channels, you should use at least twice as many buffers as channels to ensure that the channels do not have to wait for data.
Use multiple target devices, and the backup images must be directed to local devices.

Offline versus Online Backup

Database backup can be performed either offline or online:

Offline Backup

If the backup is performed offline, then only the backup task can be connected to the database partition.
An offline backup is the only backup supported when circular logging is used.
No connection is allowed when the backup task is running.
Concurrent offline backup of both catalog partition and non-catalog partitions is not possible.

Online Backup

If the backup is performed online, other applications or processes can continue to connect to the database while the backup task is running.
An online backup is supported when archive logging is enabled.
While the online backup operation is running, changes can be performed on the tables.
At the end of an online backup, the current archive log is truncated and made available for offline archival.

Backup Considerations

There are a number of points to consider when planning the use of BACKUP command:

For disaster recovery support, it is necessary to perform a full database backup as the starting point for recovery.
It is best to take table space level backups of the DB2 catalog table space (SYSCATSPACE). This will allow the restore of a damaged DB2 catalog to be as quick as possible.
Online backup, either table space level or full database, requires the retention of the archive DB2 logs at the time of the backup, for use in the restore and rollforward process. If the logs are not available, the database or table space cannot be recovered using that backup.
Selected table space backup that includes all of the table spaces related to a single application could be useful to perform point-in-time recovery of an application to a point prior to the execution of an erroneous application process.
Using the DB2 incremental backup options can reduce the size and duration of periodic DB2 backups by including only the changed portion of the database or table space.