Creates a backup copy of a database or a table space. A backup of your DB2® database and related stored data should be created to prevent data loss in the event of a database service outage.
For information about 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 database partition. 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 authorities:
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-' '-EXCLUDE-' '-COMPROPTS -string-' |
'-ENCRYPT--+---------------+--+---------+--+------------------+----'
'-ENCRLIB--name-' '-EXCLUDE-' '-ENCROPTS -string-'
>--+------------------------------------+--+--------------+----->
'-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 noncumulative (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)
as the target device.
- 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).
- LIBRARY library-name
- Integrated into IBM® DB2
Server is a DB2 ACS API driver
for the following storage hardware:
- IBM TotalStorage SAN Volume
Controller
- IBM Enterprise Storage Server® Model
800
- IBM Storwize® V7000
- IBM System Storage® DS6000™
- IBM System Storage DS8000®
- IBM System Storage N Series
- IBM XIV®
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.
- 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 default buffer size included with DB2 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|ENCRYPT
- Indicates that the backup is to be compressed or
encrypted. You cannot specify both parameters simultaneously. The COMPRESS and ENCRYPT parameters
are synonyms and can be used interchangeably only when either COMPRLIB or ENCRLIB is
specified too. If you specify COMPRESS without COMPRLIB,
the default compression library libdb2compr.so is
used for compression. If you specify ENCRYPT without ENCRLIB,
the default encryption library libdb2encr.so is
used for encryption. If you want to specify another library, you can
use COMPRESS and ENCRYPT interchangeably. You can use either COMPRLIB or ENCRLIB to
specify the libdb2compr_encr.so library. If
the encrlib and encropts database
configuration parameters are set to a non-NULL value, neither COMPRLIB or ENCRLIB nor COMPROPTS or ENCROPTS can
be specified.
- COMPRLIB|ENCRLIB name
- Indicates the name of the library that is used during the compression
or encryption process. For example, db2compr.dll for Windows; libdb2compr.so for Linux and UNIX operating systems. The name must be a fully
qualified path that refers to a file on the server. If this parameter
is not specified, the default DB2 compression
library is used. If the specified library cannot be loaded, the backup
operation fails.
- EXCLUDE
- Indicates that the library is not stored in the backup
image.
- COMPROPTS|ENCROPTS string
- Describes a block of binary data that is passed to the initialization
routine in the library. The database manager passes this string directly
from the client to the server. Any issues of byte reversal or code
page conversion are handled by the compression library. If the first
character of the data block is '@', the rest of the data is interpreted
as the name of a file on the server. The database manager then replaces
the contents of the string with the contents of this file and passes
this new value to the initialization routine. The maximum length for string is
1024 bytes.
For the default DB2 libraries libdb2compr_encr.so (compression
and encryption) or libdb2encr.so (encryption
only), the format of the ENCROPTS string is
as follows:Cipher=cipher-name:Mode=mode-name:Key Length=key-length:
Master Key Label=label-name-1...:Master Key Label=label-name-n
- Cipher is optional. Valid values are AES and 3DES (the default
is AES).
- Mode is optional. The default is CBC.
- Key length is optional. Valid values for AES are 128, 192, and
256 (the default is 256), and the only valid value for 3DES is 168.
- Master key label is optional. The default is the database master
key label.
If you are using other libraries, the format of
the ENCROPTS string depends
on those libraries.
- EXCLUDE LOGS
- Specifies that the backup image should not include any log files.
When performing an offline backup operation, logs are excluded whether
or not this option is specified, with the exception of snapshot backups.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
the EXCLUDE LOGS with a snapshot backup, writes
to log files are allowed during the backup. These log files will be
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 midweek 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 about 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.
- You
can use the DB2_BCKP_PAGE_VERIFICATION registry
variable to enable DMS and AS page validation during the backup.
- You can use the DB2_BCKP_INCLUDE_LOGS_WARNING registry
variable to specify that some online backups can now succeed even
if they have not successfully included all of the required logs.
- After you issue a BACKUP DATABASE command
with the ONLINE option and the INCLUDE
LOGS option, the resulting backed-up database image includes
all the log files necessary to roll forward to the end of backup.
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 operationColumn 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 nonpartitioned 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.