Section 13.4. Performing Database and Table Space Backups

13.4. Performing Database and Table Space Backups

There are two different granularities that you can choose for your backups, and you have two different options for how the backup can be performed. You can choose to back up the entire database or one or more table spaces from within the database. You can also choose whether you want the backup to be taken online, meaning regular database access is permitted while the backup is taken, or offline, meaning that no database access is permitted while the backup is take. All four of these options can be combined to give you a very flexible recovery mechanism for your databases.

13.4.1. Online Access Versus Offline Access

In the following sections we use the terms "online" and "offline" quite often. An online operation (backup, restore, or roll forward) allows other applications or processes to connect to the database, as well as read and modify data while the operation is running. An offline operation does not allow other applications or processes access to the database and its objects while the operation is being performed.

13.4.2. Database Backup

A database backup is a complete copy of your database objects. Besides the data, a backup copy contains information about the table spaces, containers, the database configuration file, the log control file, and the recovery history file. Note that a backup does not store the Database Manager Configuration file or the values of registry variables. Only the database configuration file is backed up.

You must have SYSADM, SYSCTRL, or SYSMAINT authority to perform a backup.

The following is the syntax for the BACKUP DATABASE command:

[View full width]
 BACKUP DATABASE database_alias [USER username [USING password]] [TABLESPACE (tblspace-name [{,tblspace-name} ... ])] [ONLINE] [INCREMENTAL [DELTA]] [USE {(TSM | XBSA) OPTIONS ("option string" | @ filename)]} [OPEN  num-sess SESSIONS]] | TO dir/dev [{,dir/dev} ... ] | LOAD lib-name OPTIONS ("option string" | @ filename)] [OPEN  num-sess SESSIONS]] [WITH num-buff BUFFERS] [BUFFER buffer-size] [PARALLELISM n] [COMPRESS [COMPRLIB name [EXCLUDE]] [COMPROPTS string]] [UTIL_IMPACT_PRIORITY priority] [EXCLUDE LOGS| INCLUDE LOGS] [WITHOUT PROMPTING] 

• To perform an offline backup of the sample database and store the backup copy in the directory d:\mybackups, use the following syntax for Windows:

   BACKUP DATABASE sample TO d:\mybackups 

  
  

• To perform an offline backup of the sample database and store the backup copy in two separate directories, use the following syntax for Linux/UNIX:

  
  BACKUP DATABASE sample                   (1)
  TO /db2backup/dir1, /db2backup/dir2      (2)
  WITH 4 BUFFERS                           (3)
  BUFFER 4096                              (4)
  PARALLELISM 2                            (5)
  

  where:

  1. Indicates the name (or alias) of the database to back up.

  2. Specifies the location(s) where you want to store the backup file. DB2 will write to both locations in parallel.

  3. Indicates how many buffers from memory can be used during the backup operation. Using more than one buffer can improve performance.

  4. Indicates the size of each buffer in 4KB pages.

  5. Specifies how many media reader and writer processes or threads are used to take the backup.

If not specified, DB2 automatically chooses optimal values for the number of buffers, the buffer size, and the parallelism settings. The values will be based on the amount of utility heap memory available, the number of processors available, and the database configuration. The objective is to minimize the time it takes to complete a backup operation.

There is no keyword OFFLINE in the syntax, as this is the default mode.

• If you have a 24x7 database, shutting down the database is not an option. To perform backups to ensure the database's recoverability, you can perform online backups instead. You must specify the keyword ONLINE in the BACKUP DATABASE command:

   BACKUP DATABASE sample ONLINE TO /dev/rdir1, /dev/rdir2 

  
  

Since there are users accessing the database while it is being backed up, it is likely that some of the changes made by these users will not be stored in the backup. A transaction may be in the middle of processing when the backup was taken. This means the backup image contains a database in an inconsistent state.

If this online backup is used to restore a database, as soon as the restore operation finishes, DB2 places the database in roll forward pending state. A roll forward operation must be performed to bring the database back to a consistent state before you can use it. If you have set LOGARCHMETH1 to USEREXIT, DISK, TSM, or VENDOR, DB2 automatically retrieves the logs into the active log directory. Otherwise, if LOGRETAIN was set, you must retrieve the log files manually before rolling forward the database. To perform the roll forward, all logs that were active at the time of the backup must be in the active log directory.

NOTE

Archival logging must be enabled to perform online backups.

Version 8.2 has a new option in the BACKUP DATABASE utility called INCLUDE LOGS. When you specify this, the logs will be backed up along with the database during an online backup operation. This ensures that if the archived logs are not available, the backup will still be restored to a minimum Point In Time (PIT) using the logs that are included in the backup image. If you want to restore to a later PIT, additional log files may be required.

For example, to take an online backup of the SAMPLE database along with the logs, using the destination directory /dev/rdir1, issue:

 BACKUP DATABASE sample ONLINE TO /dev/rdir1 INCLUDE LOGS 

13.4.3. Table Space Backup

In a database where only some of your table spaces change considerably, you may opt not to back up the entire database but only specific table spaces. To perform a table space backup you can use the following syntax:

 BACKUP DATABASE sample TABLESPACE (syscatspace, userspace1, userspace2) ONLINE TO /db2tbsp/backup1, /db2tbsp/backup2 

The keyword TABLESPACE indicates this is a table space backup, not a full database backup. You can also see from the example that you can include as many table spaces as desired in the backup. Temporary table spaces cannot be backed up using a table space-level backup.

You will usually back up related table spaces together. For example, if using DMS table spaces where one table space is used for the table data, another one for the indexes, and another one for LOBs, you should back up all of these table spaces at the same time so that you have consistent information. This is also true for table spaces containing tables defined with referential constraints between them.

13.4.4. Incremental Backups

As database sizes continue to expand, the time and hardware resources required to back up and recover these databases also grows substantially. Full database and table space backups are not always the best approach when dealing with large databases, because the storage requirements for multiple copies of such databases are enormous.

To address this issue, DB2 provides incremental backup and recovery. An incremental backup is a backup image that contains only pages that have been updated since the previous backup was taken. In addition to updated data and index pages, each incremental backup image also contains all of the initial database metadata (such as database configuration, table space definitions, database history, and so on) that is normally stored in full backup images.

There are two kinds of incremental backups.

• In incremental cumulative backups, DB2 backs up all of the data that has changed since the last full database backup.

• In delta backups.DB2 backs up only the data that has changed since the last successful full, cumulative, or delta backup.

Figure 13.4 illustrates these concepts.

Figure 13.4. Incremental and delta backups

For incremental backups, if there was a crash after the incremental backup on Friday, you would restore the first Sunday full backup, followed by the incremental backup taken on Friday.

For delta backups, if there was a crash after the delta backup on Friday, you would restore the first Sunday full backup, followed by each of the delta backups taken from Monday until Friday inclusive.

To enable incremental and delta backups, the TRACKMOD database parameter must be set to YES. This allows the database manager to track database modifications so that the backup database utility can detect which subsets of the database pages must be examined by an incremental backup and potentially included in the backup image. After setting this parameter to YES, you must take a full database backup to have a baseline against which incremental backups can be taken.

For example, to perform a cumulative incremental backup on the SAMPLE database to destination directory /dev/rdir1, issue:

 BACKUP DB sample INCREMENTAL TO /dev/rdir1 

To perform a delta backup on the SAMPLE database to destination directory /dev/rdir1, issue:

 BACKUP DB sample INCREMENTAL DELTA TO /dev/rdir1 

13.4.5. Performing Backups with the Control Center

You can use the Backup Wizard to perform backups. From the Control Center, expand your database folder, right-click on the database name you wish to back up and select Backup. The database Backup Wizard appears. Figure 13.5 shows that you can choose to perform either a database-level backup or a table space-level backup. From here, the Backup Wizard will guide you through backup command options.

Figure 13.5. The Backup Wizard

13.4.6. The Backup Files

The backup images are stored as files. The name of a backup file contains the following parts:

• Database alias

• Type of backup (0=Full database, 3=Table space, 4=Copy from LOAD)

• Instance name

• Database partition (always NODE0000 for a single-partition database)

• Catalog partition number (always CATN0000 for a single-partition database)

• Timestamp of the backup

• The image sequence number

The exact naming convention varies slightly by platform. In Figure 13.6, you can see that on Windows systems the file that actually contains the backup image is 131259.001.

Figure 13.6. Backup file name hierarchy

When DB2 backs up a database on Windows, it creates a hierarchy of directories. For example, the command

 BACKUP DATABASE sample to D:\temp 

produces the directories and backup image shown in Figure 13.7.

Figure 13.7. Backup file hierarchy on Windows

When performing a restore, you specify the directory that was specified in the BACKUP DATABASE command, not the subdirectory where the backup image actually resides.

On Linux and UNIX systems, DB2 does not create additional directories. The backup image can be found in the directory specified in the BACKUP DATABASE command, or the current directory where the command is issued.