DB2 Version 9.7 for Linux, UNIX, and Windows

BACKUP DATABASE command using the ADMIN_CMD procedure

Creates a backup copy of a database or a table space.

For information on the backup operations supported by DB2® database systems between different operating systems and hardware platforms, see "Backup and restore operations between different operating systems and hardware platforms".

Scope

In a partitioned database environment, if no database partitions are specified, this command affects only the database partition on which it is executed.

If the option to perform a partitioned backup is specified, the command can be called only on the catalog node. If the option specifies that all database partition servers are to be backed up, it affects all database partition servers that are listed in the db2nodes.cfg file. Otherwise, it affects the database partition servers that are specified on the command.

Authorization

One of the following:

SYSADM
SYSCTRL
SYSMAINT

Required connection

Database. The existing database connection remains after the completion of the backup operation.

Command syntax


>>-BACKUP--+-DATABASE-+--database-alias------------------------->
           '-DB-------'                   

>--+---------------------------------------------------------------------------------------+-->
   '-ON--+-+-DBPARTITIONNUM--+--| Partition number(s) |----------------------------------+-'   
         | '-DBPARTITIONNUMS-'                                                           |     
         '-ALL DBPARTITIONNUMS--+------------------------------------------------------+-'     
                                '-EXCEPT--+-DBPARTITIONNUM--+--| Partition number(s) |-'       
                                          '-DBPARTITIONNUMS-'                                  

                                              .-ONLINE-.   
>--+---------------------------------------+--+--------+-------->
   |                .-,---------------.    |               
   |                V                 |    |               
   '-TABLESPACE--(----tablespace-name-+--)-'               

>--+------------------------+----------------------------------->
   '-INCREMENTAL--+-------+-'   
                  '-DELTA-'     

>--+-----------------------------------------------------------+-->
   +-USE--+-+-TSM--+-| Open sessions |----------+--| Options |-+   
   |      | '-XBSA-'                            |              |   
   |      +-SNAPSHOT--+-----------------------+-+              |   
   |      |           '-LIBRARY--library-name-' |              |   
   |      '-SCRIPT--script-name-----------------'              |   
   +-LOAD--library-name--| Open sessions |--| Options |--------+   
   |     .-,-------.                                           |   
   |     V         |                                           |   
   '-TO----+-dir-+-+-------------------------------------------'   
           '-dev-'                                                 

>--+--------------+--+----------------------------+------------->
   '-DEDUP_DEVICE-'  '-WITH--num-buffers--BUFFERS-'   

>--+---------------------+--+----------------+------------------>
   '-BUFFER--buffer-size-'  '-PARALLELISM--n-'   

>--+------------------------------------------------------------------+-->
   '-COMPRESS--+-----------------------------+--+-------------------+-'   
               '-COMPRLIB--name--+---------+-'  '-COMPROPTS -string-'     
                                 '-EXCLUDE-'                              

>--+------------------------------------+--+--------------+----->
   '-UTIL_IMPACT_PRIORITY--+----------+-'  +-EXCLUDE LOGS-+   
                           '-priority-'    '-INCLUDE LOGS-'   

   .-WITHOUT PROMPTING-.   
>--+-------------------+---------------------------------------><

Partition number(s)

      .-,--------------------------------------------------.      
      V                                                    |      
|--(----db-partition-number1--+--------------------------+-+--)--|
                              '-TO--db-partition-number2-'        

Open sessions

|--+------------------------------+-----------------------------|
   '-OPEN--num-sessions--SESSIONS-'   

Options

|--+-------------------------------+----------------------------|
   '-OPTIONS--+-"options-string"-+-'   
              '-@--file-name-----'

Command parameters

DATABASE | DB database-alias

Specifies the alias of the database to back up. The alias must be a local database defined on the server and must be the database name that the user is currently connected to. If the database-alias is not the one the user is connected to, an SQL20322N error is returned.

ON

Backup the database on a set of database partitions. This clause shall be specified only on the catalog partition.

DBPARTITIONNUM db-partition-number1: Specifies a database partition number in the database partition list.

DBPARTITIONNUMS db-partition-number1 TO db-partition-number2

Specifies a range of database partition numbers, so that all partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

ALL DBPARTITIONNUMS

Specifies that the database is to be backed up on all partitions specified in the db2nodes.cfg file.

EXCEPT

Specifies that the database is to be backed up on all partitions specified in the db2nodes.cfg file, except those specified in the database partition list.

DBPARTITIONNUM db-partition-number1: Specifies a database partition number in the database partition list.
DBPARTITIONNUMS db-partition-number1 TO db-partition-number2: Specifies a range of database partition numbers, so that all partitions from db-partition-number1 up to and including db-partition-number2 are included in the database partition list.

TABLESPACE tablespace-name

A list of names used to specify the table spaces to be backed up.

ONLINE

Specifies online backup. This is the only supported mode and is the default. The ONLINE clause does not need to be specified.

INCREMENTAL

Specifies a cumulative (incremental) backup image. An incremental backup image is a copy of all database data that has changed since the most recent successful, full backup operation.

DELTA: Specifies a non-cumulative (delta) backup image. A delta backup image is a copy of all database data that has changed since the most recent successful backup operation of any type.

USE

TSM

Specifies that the backup is to use Tivoli® Storage Manager (TSM) output.

XBSA

Specifies that the XBSA interface is to be used. Backup Services APIs (XBSA) are an open application programming interface for applications or facilities needing data storage management for backup or archiving purposes.

SNAPSHOT

Specifies that a snapshot backup is to be taken.

You cannot use the SNAPSHOT parameter with any of the following parameters:

TABLESPACE
INCREMENTAL
WITH num-buffers BUFFERS
BUFFER
PARALLELISM
COMPRESS
UTIL_IMPACT_PRIORITY
SESSIONS

The default behavior for a snapshot backup is a FULL DATABASE OFFLINE backup of all paths that make up the database including all containers, local volume directory, database path (DBPATH), and primary log and mirror log paths (INCLUDE LOGS is the default for all snapshot backups unless EXCLUDE LOGS is explicitly stated).

Only one snapshot can be run at a time and only database snapshot operations are permitted. When restoring on a different system that would depend on the access to the storage as long as the database paths are the same. You may use db2relocatedb afterwords to change the instance after the restore completes.

LIBRARY library-name

Integrated into IBM® Data Server is a DB2 ACS API driver for the following storage hardware:

IBM TotalStorage SAN Volume Controller
IBM Enterprise Storage Server® Model 800
IBM System Storage® DS6000™
IBM System Storage DS8000®
IBM System Storage N Series
IBM XIV
NetApp V-series
NetApp FAS

If you have other storage hardware, and a DB2 ACS API driver for that storage hardware, you can use the LIBRARY parameter to specify the DB2 ACS API driver.

The value of the LIBRARY parameter is a fully-qualified library file name.

SCRIPT script-name

The name of the executable script capable of performing a snapshot backup operation. The script name must be a fully qualified file name.

OPTIONS

"options-string": Specifies options to be used for the backup operation. The string will be passed exactly as it was entered, without the double quotation marks.
@ file-name: Specifies that the options to be used for the backup operation are contained in a file located on the DB2 server. The string will be passed to the vendor support library. The file must be a fully qualified file name.

You cannot use the vendoropt database configuration parameter to specify vendor-specific options for snapshot backup operations. You must use the OPTIONS parameter of the backup utilities instead.

OPEN num-sessions SESSIONS

The number of I/O sessions to create between the DB2 product and the TSM product or another backup vendor product. This parameter has no effect when you back up to tape, disk, or other local device. If you specify the INCLUDE LOGS parameter for an online backup, an extra session is created for the OPEN num-sessions SESSIONS parameter after the initial sessions are closed. If you are creating a Single System View (SSV) online backup, for each node backed up, an extra session is created for the OPEN num-sessions SESSIONS parameter after the initial sessions are closed. If you use this parameter with the TSM option, the number of entries that are created in the history file is equal to the number of sessions created.

TO dir | dev

A list of directory or tape device names. The full path on which the directory resides must be specified. This target directory or device must be locally addressable on the database server.

In a partitioned database, the target directory or device must exist on all database partitions, and can optionally be a shared path. The directory or device name may be specified using a database partition expression. For more information about database partition expressions, see "Automatic storage databases".

This parameter can be repeated to specify the target directories and devices that the backup image will span. If more than one target is specified (target1, target2, and target3, for example), target1 will be opened first. The media header and special files (including the configuration file, table space table, and history file) are placed in target1. All remaining targets are opened, and are then used in parallel during the backup operation. Because there is no general tape support on Windows operating systems, each type of tape device requires a unique device driver.

Use of tape devices or floppy disks might require prompts and user interaction, which will result in an error being returned.

If the tape system does not support the ability to uniquely reference a backup image, it is recommended that multiple backup copies of the same database not be kept on the same tape.

LOAD library-name

The name of the shared library (DLL on Windows operating systems) containing the vendor backup and restore I/O functions to be used. It can contain the full path. If the full path is not given, it will default to the path on which the user exit program resides.

DEDUP_DEVICE

Optimizes the format of the backup images for target storage devices that support data deduplication. Available in Version 9.7 Fix Pack 4 and later fix packs.

WITH num-buffers BUFFERS

The number of buffers to be used. If the number of buffers that you specify is not enough to create a successful backup, then the minimum value necessary to complete the backup is automatically chosen for this parameter. If you are backing up to multiple locations, you can specify a larger number of buffers to improve performance. If you specify the COMPRESS parameter, to help improve performance, you can add an extra buffer for each table space that you specify for the PARALLELISM parameter.

BUFFER buffer-size

The size, in 4 KB pages, of the buffer used when building the backup image. DB2 will automatically choose an optimal value for this parameter unless you explicitly enter a value. The minimum value for this parameter is 8 pages.

If using tape with variable block size, reduce the buffer size to within the range that the tape device supports. Otherwise, the backup operation might succeed, but the resulting image might not be recoverable.

With most versions of Linux, using the DB2 default buffer size for backup operations to a SCSI tape device results in error SQL2025N, reason code 75. To prevent the overflow of Linux internal SCSI buffers, use this formula:

bufferpages <= ST_MAX_BUFFERS * ST_BUFFER_BLOCKS / 4

where bufferpages is the value you want to use with the BUFFER parameter, and ST_MAX_BUFFERS and ST_BUFFER_BLOCKS are defined in the Linux kernel under the drivers/scsi directory.

PARALLELISM n

Determines the number of table spaces which can be read in parallel by the backup utility. DB2 will automatically choose an optimal value for this parameter unless you explicitly enter a value.

UTIL_IMPACT_PRIORITY priority

Specifies that the backup will run in throttled mode, with the priority specified. Throttling allows you to regulate the performance impact of the backup operation. Priority can be any number between 1 and 100, with 1 representing the lowest priority, and 100 representing the highest priority. If the UTIL_IMPACT_PRIORITY keyword is specified with no priority, the backup will run with the default priority of 50. If UTIL_IMPACT_PRIORITY is not specified, the backup will run in unthrottled mode. An impact policy must be defined by setting the util_impact_lim configuration parameter for a backup to run in throttled mode.

COMPRESS

Indicates that the backup is to be compressed.

COMPRLIB name: Indicates the name of the library to be used to perform the compression (for example, db2compr.dll for Windows; libdb2compr.so for Linux or UNIX systems). The name must be a fully qualified path referring to a file on the server. If this parameter is not specified, the default DB2 compression library will be used. If the specified library cannot be loaded, the backup will fail.
EXCLUDE: Indicates that the compression library will not be stored in the backup image.
COMPROPTS string: Describes a block of binary data that will be passed to the initialization routine in the compression library. DB2 will pass this string directly from the client to the server, so any issues of byte reversal or code page conversion will have to be handled by the compression library. If the first character of the data block is '@', the remainder of the data will be interpreted by DB2 as the name of a file residing on the server. DB2 will then replace the contents of string with the contents of this file and will pass this new value to the initialization routine instead. The maximum length for string is 1024 bytes.

EXCLUDE LOGS

Specifies that the backup image should not include any log files. Logs are excluded by default in the following backup scenarios:

Offline backup of a single-partitioned database.
Online or offline backup of a multi-partitioned database, when not using a single system view backup.

If you specify EXCLUDE LOGS with a snapshot backup, log files can be written to during the backup. These log files are included by default in the snapshot backup, but are not usable for recovery. If this backup is restored, the log files must not be extracted from the backup. If the log path was set to the default when the backup was taken, then it is not possible to exclude the log files from being restored and they must be deleted manually after the backup is restored. If the log path was not the default, then the log files can be excluded at restore time by using the LOGTARGET EXCLUDE options with the RESTORE DATABASE command.

INCLUDE LOGS

Specifies that the backup image should include the range of log files required to restore and roll forward this image to some consistent point in time. This option is not valid for an offline backup, with the exception of snapshot backups. INCLUDE LOGS is always the default option for any online backup operation, except a multi-partitioned online backup where each database partition is backed up independently (that is, a non-single system view backup).

If any of the log files that are required for the backup have previously been backed up and are no longer in the log path, then the DB2 database manager retrieves them for backup from the overflow log path, if the path has been set. Otherwise, the database manager retrieves them for backup from the current log path or mirror log path. These log files are removed from the log path after the backup has completed.

WITHOUT PROMPTING

Specifies that the backup will run unattended, and that any actions which normally require user intervention will return an error message. This is the default.

Examples

The 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:

(Sun) CALL SYSPROC.ADMIN_CMD('backup db sample online use tsm')
(Mon) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental delta use tsm')
(Tue) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental delta use tsm')
(Wed) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental use tsm')
(Thu) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental delta use tsm')
(Fri) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental delta use tsm')
(Sat) CALL SYSPROC.ADMIN_CMD
      ('backup db sample online incremental use tsm')

Usage notes

The data in a backup cannot be protected by the database server. Make sure that backups are properly safeguarded, particularly if the backup contains LBAC-protected data.
When backing up to tape, use of a variable block size is currently not supported. If you must use this option, ensure that you have well tested procedures in place that enable you to recover successfully, using backup images that were created with a variable block size.
The backup utility cannot be used with a Type 2 connection.
When using a variable block size, you must specify a backup buffer size that is less than or equal to the maximum limit for the tape devices that you are using. For optimal performance, the buffer size must be equal to the maximum block size limit of the device being used.
Snapshot backups should be complemented with regular disk backups in case of failure in the filer/storage system.
As you regularly backup your database, you might accumulate very large database backup images, many database logs and load copy images, all of which might be taking up a large amount of disk space. Refer to "Managing recovery objects" for information on how to manage these recovery objects.
You can use the OPTIONS parameter to enable backup operations in TSM environments supporting proxy nodes. For more information, see the "Configuring a Tivoli Storage Manager client" topic.

Result set information

Command execution status is returned in the SQLCA resulting from the CALL statement. If execution is successful, the command returns additional information. The backup operation will return one result set, comprising one row per database partition that participated in the backup.

Table 1. Result set for a backup operation
Column name	Data type	Description
BACKUP_TIME	VARCHAR(14)	Corresponds to the timestamp string used to name the backup image.
DBPARTITIONNUM	SMALLINT	The database partition number on which the agent executed the backup operation.
SQLCODE	INTEGER	Final SQLCODE resulting from the backup processing on the specified database partition.
SQLERRMC	VARCHAR(70)	Final SQLERRMC resulting from the backup processing on the specified database partition.
SQLERRML	SMALLINT	Final SQLERRML resulting from the backup processing on the specified database partition.

If a non-partitioned database is backed up, or if a partitioned database is backed up using the traditional single-partition syntax, the result set will comprise a single row. DBPARTITIONNUM will contain the identifier number of the database partition being backed up.

SQLCODE, SQLERRMC, and SQLERRML refer to the equivalently-named members of the SQLCA that is returned by the backup on the specified database partition.