Backing up data using the db_backup command

Note: This topic only applies to using db_backup in IAS. For using it in a standalone Db2® Warehouse, see Backing up by using the db_backup command.

Run the db_backup command to back up an entire database, or just a schema.

The backup methods used for BLUDB database in Integrated Analytics System are Single System View (SSV) and granular backup. These backups provide a single time-stamp for all database partitions, making the recovery simpler. The backup command can be invoked only from the Catalog node, but each MLN (Multiple Logical Node) participates in the backup operation.

Important:
  • The backup command can be invoked only from the Catalog node. When connected to the BLUDB database, type the command:
    db2 values current node
    If the output of the command shows a value of 0, you are logged on to the Catalog node.
  • If the db_backup command is issued from within an SSH command, the SSH command must be run outside the container, and the db_backup command must be issued as a sudo command. For example:
    ssh -p 50022 bluadmin@9.32.246.72 "sudo -E db_backup -path /scratch/ -type off"

Run the db_backup command as the bluadmin or database admin user.

For a schema backup to process tables that are enabled for Row and Column Access Control (RCAC), a user must be granted the LOGICAL BACKUP AND RESTORE OF RCAC PROTECTED DATA privilege. This is not required for the bluadmin user.

Supported database backup types

  • Full offline backups
  • Full online backups
  • Incremental online backups

Supported schema backup types

  • Full online backups
  • Incremental online backups
  • Delta incremental online backups
Note: For more information, refer to Incremental schema backup and restore.

Supported backup destinations

The following backup destinations are supported:
  • SAN storage
  • NAS storage
  • IBM Spectrum Protect server - LAN Based (since v1.0.7.0) - database only
  • IBM Spectrum Protect server - LAN Free (since v1.0.9.0) - database only
  • EMC NetWorker (since v1.0.9.0) - database only

db_backup command syntax

For a database backup:
db_backup -type {off|onl|inc} -path path [-partitioned-backup]

db_backup -type {off|onl|inc} -tsm [-sessions {1-8}]
db_backup -type {off|onl|inc} -emc [-sessions >=1]
For a schema backup:
db_backup -type {onl|inc|del} -path path -schema schema_name [-schemafile filepath]
[-compress {gzip | lz4 | no}] [-format {binary | text}] [-sessions N] [-image-check [hash | size | rowcount]]
To display history or other command usage information:
db_backup -history
db_backup -version
db_backup -status
db_backup {-h|--help}
-path
A target directory or device. The full path on which the directory resides must be specified. This target directory or device must be locally addressable on the database server.
Multiple paths are supported for both database and schema backups and can be specified during the backup to improve the performance.For a schema backup, ensure that the paths do not share the same underlying storage or filesystem. For example, /scratch/backup1 and /scratch/backup2 cannot be used because they share the same underlying /scratch filesystem.
Remember: In Integrated Analytics System, the target directory or device must exist on all database partitions.
-partitioned-backup
Directs backup to create a separate subfolder under the provided path for each MLN using format 'MLN $4N'.
$4N is a 4-digit number of the node.
-tsm
Specifies to run the backup directly to IBM Spectrum Protect.
-emc
Specifies to run the backup directly to EMC NetWorker.
-type
Specifies the type of backup to run.
  • off - Offline - Specifies full offline backup.
  • onl - Online - Specifies full online backup. Online backups are only available for databases configured with logarchmeth1 (Database Configuration) enabled. The default is set to the /scratch/db2archive/archive_log/ directory.
  • inc - Incremental online - An incremental backup image is a copy of all data that has changed since the most recent, successful, full online backup operation. This is also known as a cumulative backup image, because a series of incremental backups taken over time will each have the contents of the previous incremental backup image. The predecessor of an incremental backup image is always the most recent successful full online backup of the same object. Incremental schema backups can only be performed on schemas enabled for row modification tracking.
  • del - Delta incremental - A delta incremental online backup, also known as a differential backup. A delta backup image is a copy of all data that has changed since the last successful backup. This could be a full, cumulative or a differential backup. Delta incremental schema backups can only be performed on schemas enabled for row modification tracking.
-compress
Optional parameter, used only with -schema option. By default, -compress is set to lz4. If specified, the data files in the backup are compressed. The gzip and lz4 formats are supported.
-sessions
Specifies the number of I/O sessions between Integrated Analytics System and IBM Spectrum Protect (-tsm) or EMC Net Worker (-emc). This parameter has no effect when you back up to tape, disk, or another local device. For IBM Spectrum Protect, the recommended number of sessions is 4; the default number is 1. For EMC NetWorker, the number of sessions can be more than 1 based on EMC NetWorker configurations; the default number is 1.
Note: For IBM Spectrum Protect, the -sessions option refers to the number of sessions per database partition (MLN).
-schema
The schema to be backed up. When not specified, the whole database is backed up. Multiple schema names can be specified and the schema names must be separated by a space. However, schemas enabled for row modification tracking (RMT), can only have a single schema specified. For more information, refer to Schema enabled for row modification tracking.
-format
Optional. Used only with the -schema parameter. By default, the table data files are generated in a binary format. If specified, the data files in the backup will be generated in a text format.
-schemafile
Specifies a full path with the file name. Each schema name in the file is case sensitive and must be provided in a separate line.
-history
Displays up to 10 backup images.
If more than 10 backups are done, information about the latest 10 backups is displayed. The rest of the information is redirected to the /scratch/bluadmin_BNR/backup_history.txt file.
For incremental backups, history does not list backups that are not part of the current backup chain.
Note:

/scratch/bluadmin_BNR/backup_history.txt is not updated after every backup. It is only updated on db_backup -history command invocation.

If db_backup -history command fails, the work around is:

  1. Check /scratch/bluadmin_BNR/backup_history.txt
  2. If the file does not exist or is not updated, use db2 list history command as described in https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001991.html
-status
Displays if another db_backup or db_restore is in progress.
Each job is displayed in a separate line.
-abort PID
Aborts backup jobs that run under PID. To list currently active jobs and corresponding PIDs, run the db_backup -list command.
-version
Displays information about the installed version of db_backup.
-sessions N
Optional. Spawns N threads to back up individual tables concurrently. The default value is 4. For multi-schema backups, N threads are spawned for each schema.
-image-check [hash|size|rowcount]

Optional. Post-process schema backup files to enable image integrity verification prior to schema-level restore. All values can be selected. No value is set by default. However, a list of names of all files in backup image is always recorded and verified before restore.

hash
Computes and records hash values for all files in a backup image.
size
Records sizes of all files in a backup image.
rowcount
Processes data files and records the number of rows.

Backup examples

Multiple schemas backup:
db_backup -schema schema1 schema2 -path /backup/Backup-Demo/Onl-bkp/ -type onl
Multiple schemas backup using a file: 
db_backup -schemafile /backup/Backup-Demo/Schema-File -path /backup/Backup-Demo/Onl-bkp/ -type onl
Full offline backup:
db_backup -path /backup/Backup-Demo/Off-bkp -type off
Full offline backup to IBM Spectrum Protect:
db_backup -type off -tsm
db_backup -type off -tsm -sessions 2
Full online backup:
db_backup -path /backup/Backup-Demo/Onl-bkp -type onl
Online backup to IBM Spectrum Protect (takes default session 1):
db_backup -type onl -tsm
db_backup -type onl -tsm -sessions 4
Incremental online backup:
db_backup -path /backup/Backup-Demo/online-inc -type inc
Incremental backup to IBM Spectrum Protect:
db_backup -type inc -tsm
db_backup -type inc -tsm -sessions 6
Backup with multiple destination paths (comma-separated):
db_backup -path /backup/Backup-Demo/Onl-bkp/path1, /backup/Backup-Demo/Onl-bkp/path2 -type onl
db_backup -path /backup/Backup-Demo/Onl-bkp/path1,/backup/Backup-Demo/Onl-bkp/path2 -type onl
Check backup history:
db_backup -history
Check backup version:
db_backup -version
Check backup status:
bd_backup -status
Full offline backup to EMC NetWorker:
db_backup -type off -emc
db_backup -type off -emc -sessions 2
Online backup to EMC NetWorker:
db_backup -type onl -emc
db_backup -type onl -emc -sessions 5
Incremental backup to EMC NetWorker:
db_backup -type inc -emc
db_backup -type inc -emc -sessions 10
Backup schema:
db_backup -schema gbnr_schema -path /backup/Backup-Demo/Onl-bkp/ -type onl
Backup schema with delimited name:
db_backup -schema '"Gbnr sChEmA"' -path /backup/Backup-Demo/Inc-bkp/ -type inc
Backup schema with the compress gzip option:
db_backup -schema gbnr_schema -compress gzip -path /backup/Backup-Demo/Del-bkp/ -type del
Backup schema with the compress lz4 option:
db_backup -schema gbnr_schema -compress lz4 -path /backup/Backup-Demo/Onl-bkp/ -type onl
Backup schema with the format binary option: 
db_backup -schema gbnr_schema -format binary -path /backup/Backup-Demo/Inc-bkp/ -type inc
Backup schema with the format text option:
db_backup -schema gbnr_schema -format text -path /backup/Backup-Demo/Del-bkp/ -type del
Backup schema with multiple destination paths: 
db_backup -schema gbnr_schema -path /backup/Backup-Demo/Onl-bkp/path1, /backup/Backup-Demo/Onl-bkp/path2 -type onl
db_backup -schema gbnr_schema -path /backup/Backup-Demo/Onl-bkp/path1,/backup/Backup-Demo/Onl-bkp/path2 -type onl