LOGICAL_BACKUP stored procedure - for full, cumulative incremental, or delta incremental logical backups of one or more schemas

Use the Db2 logical backup feature to do full, cumulative incremental, or delta incremental backups of one or more schemas.

Authorization

The following authorities are required to run the LOGICAL_BACKUP stored procedure on a Db2® schema:
  • EXECUTE authority on the SYSPROC.LOGICAL_BACKUP stored procedure (the default setting is NOT granted to PUBLIC)
  • One of the following authorities to access table data:
    • DATAACCESS authority on the database or schema.
    • SELECTIN privilege on the schema.
    • SELECT privilege on all tables in the schema.
  • LOGICAL BACKUP AND RESTORE OF RCAC PROTECTED DATA authority on the schema, if one or more tables in the schema have Row and Column Access (RCAC) controls enabled.
    Note: As with any privilege, users are not able to grant permissions to the same user. However, it possible to grant the authority to a group or role to which the user belongs. For example, the bluadmin user is not able to grant the authority to the bluadmin user, but can grant the authority to the bluadmin group.
  • EXECUTE authority on the following procedures:
    • SYSPROC.ADMIN_GET_DEPTREE
    • SYSPROC.WLM_SET_CLIENT_INFO
    • SYSPROC.MONITOR_UTIL
    • SYSPROC.ADMIN_SET_MAINT_MODE

Scope

The SYSPROC.LOGICAL_BACKUP() command creates a backup image containing DDL and table data for all tables and objects in the schema.

Syntax

A schema-level backup can be done by passing the schema name to the LOGICAL_BACKUP stored procedure by using the -schema <schema_name> option. If an error occurs, any partial backup image that is created is removed. If the backup is successful, a timestamp is returned as part of return sqlcode. This timestamp acts as a reference label for the backup image when running a restore operation. The logical backup operation ensures that the timestamp is unique, to distinguish between backups when several backups are started at the same time.

Read syntax diagramSkip visual syntax diagramLOGICAL_BACKUP(-typeFULLINCDEL-schema <schema name>-tsm-path list-of-path(s)-s3s3-options-coscos-options-errorlogdir <path>-keeplogsSUMMARY_IN_BACKUPALL_IN_ERRORLOGDIR-keeplogsON_ERROR-media-connection-timeout <milliseconds>-sessions <N>-compressNOGZIPLZ4-formatTEXTBINARY-image-checkHASHSIZEHASH SIZE-backup-migrate)
Read syntax diagramSkip visual syntax diagrams3-options-bucket-url <bucket-url>-access-key <access-key>-secret-key <secret-key>-default-region <region>-endpoint <endpoint>-multipart-size-mb <multipart size mb>
Read syntax diagramSkip visual syntax diagramcos-options-bucket-url <bucket-url>-access-key <access-key>-secret-key <secret-key>-default-region <region>-endpoint <endpoint>-multipart-size-mb <multipart size mb>

Handling for different table types under schema backup:

  • External Tables – The definition of the external table is captured and restored, but not the contents.
  • Materialized query tables - The definition of the MQT is captured and restored, but not the contents. Ensure that you refresh the MQT after restore.
  • Temporary tables – These tables are not captured by schema backup.
  • Row-organized permanent tables in a schema with row modification tracking enabled - Any schema backup in this situation fails because these tables are not enabled for row modification tracking.

Command parameters

-type FULL|INC|DEL
  • FULL – Full backup.
  • INC – Cumulative incremental backup.
  • DEL – Delta incremental backup (also known as a differential backup).
-schema SCHEMANAME
Schema to be backed up. Multiple schema names must be separated by space.
-path
Specifies a list-of-path(s) for storing the backup image. This option is mutually exclusive with the -tsm, -s3 and -cos options.
list-of-path(s)
One or more path strings. If multiple path strings are specified, they must be separated by a space. In a DPF environment, each path has to be shared by cross-mounting in the same location on all hosts. These paths are subject to the extbl_strict_io and extbl_location configuration parameters. The backup directory and files are owned by the user that invoked the logical backup.
-tsm
Backup image is to be stored in IBM Spectrum Protect server. Mutually exclusive with path and s3 options.
-s3
Backup image is to be stored in S3 cloud. Mutually exclusive with -path, tsm, and -cos options. -bucket-url, -access-key, -secret-key, and either -default-region or -endpoint options are mandatory. If both -default-region and -endpoint are specified, then region in -endpoint must match one in -default-region. Data is sent directly to cloud, no local temp storage is required.
-cos
Backup image is to be stored in IBM COS. Mutually exclusive with -path, -tsm, and -s3 options. -bucket-url, -access-key, -secret-key, and -endpoint options are mandatory. If specified, -default-region is ignored. Data is sent directly to cloud, no local temp storage is required.
-errorlogdir <path>
Path (on server) where diagnostic log files will be saved to. Default is sqllib/tmp/bnr/logs subject to extbl_strict_io and extbl_location configuration parameters.
-keeplogs {ON_ERROR | SUMMARY_IN_BACKUP | ALL_IN_ERRORLOGDIR}
Indicates how diagnostic log files are to be handled after a successful operation.
ON_ERROR - remove all diagnostic log files.
SUMMARY_IN_BACKUP - save the main trace log to the backup image, making it part of the backup image. This is the default setting when selecting the -s3, -cos, or -tsm target options.
ALL_IN_ERRORLOGDIR - preserve all diagnostic log files in errorlogdir. This is the default for path media.
Note: On failure, all diagnostic log files are preserved irrespective of the value of -keeplogs option.
-sessions <N> (optional)
Spawns N threads/connections to backup individual tables concurrently. Default value is 4. For multi-schema backups N threads are spawned for each schema.
-compress NO|GZIP|LZ4 (optional)
Compress setting for the backup. By default, it is set to LZ4.
-format TEXT|BINARY (optional)
Format for the backup files. By default, it is set to BINARY.
-image-check [HASH|SIZE] (optional)
Post-process schema backup image files after backup operation to enable image integrity verification prior to schema-level restore. Both values can be selected. Neither value is on by default. Information is used to verify the NAME option (a list of names of all files in the backup image) is always collected.
Setting the HASH value computes and records hash values for all of the files in backup image. This value is only allowed for -path backups.
Setting the SIZE option records the sizes of all of the files in backup image.
-media-connection-timeout <milliseconds> (optional)
Maximum time (in milliseconds) to wait before TSM, S3 or IBM COS connection timeout.
-bucket-url <bucket-url>
URL of the cloud bucket.
-access-key <access-key>
Access key id for the bucket.
-secret-key <secret-key>
Secret access key for the bucket.
-default-region <region>
Default region of the bucket.
-endpoint <endpoint>
Gateway endpoint that connects to either Amazon S3 or IBM COS. If both -default-region and -endpoint are specified, then region in -endpoint must match one in -default-region.
-multipart-size-mb <multipart size mb>
Value (in MB) of -multipart-size-mb. Default is 105.
-backup-migrate (optional)
Take logical backup of a schema that is not yet enabled for row modification tracking to facilitate upcoming restore with -enable-row-modification-tracking Backup will fail if source tables are not be possible to be row modification tracking, for example organize by row. Only allowed for -type FULL operation.

New sqlcodes

On the successful running of the LOGICAL_BACKUP() stored procedure, a SQL1796I is returned.

Important: The timestamp that is returned uniquely identifies the backup image. Use it when referring to the backup image in both the LOGICAL_RESTORE and LOGICAL_BACKUP_DETAILS stored procedures.
The following example shows an SQL1796I message returned by a successful LOGICAL_BACKUP() operation:
SQL1796I  Logical backup utility has completed successfully, timestamp for the
backup image is "20220817203500".  SQLSTATE=01541
The following example shows an SQL1797N message that is returned when a LOGICAL_BACKUP() operation fails:
SQL1797N The "SYSPROC.LOGICAL_BACKUP" utility has failed with error 
"Valid path required". SQLSTATE=5UA0Q