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.

s3-options

cos-options

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) or remote-storage 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 an IBM Storage Protect, formerly Tivoli Storage Manager (TSM), server. Mutually exclusive with path and s3 options.

Note: IBM Storage Protect is the new product name for Tivoli Storage Manager (TSM).

The product now known as IBM Storage Protect was named IBM Spectrum Protect in levels earlier than 8.1.19. To learn more about the brand change, see IBM Spectrum Protect brand change to IBM Storage Protect.
The product previously known as IBM Spectrum Protect™ was named IBM Tivoli® Storage Manager in releases earlier than Version 7.1.3. To learn more about the rebranding transition, see Tivoli Storage Manager branding transition to IBM Spectrum Protect™.
IBM Spectrum Protect and IBM Tivoli Storage Manager 7.1 reached end of support (EOS) at 31 December 2021, see: IBM Spectrum Protect and Tivoli Storage Manager 7.1 products End of Support 31 December 2021.

-s3

Backup image is to be stored in the Amazon Web Services (AWS) 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 Cloud Object Storage (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 the cloud. No local temp storage is required.

-errorlogdir <path>

Path (on server) where diagnostic log files are saved. The specified path is subject to extbl_strict_io and extbl_location configuration parameters. It must not be the same path as any of the paths listed under the list-of-path(s) that are specified by the -path option. By default, this path is set to sqllib/tmp/bnr/logs.

-keeplogs {ON_ERROR | SUMMARY_IN_BACKUP | ALL_IN_ERRORLOGDIR}

Indicates how diagnostic log files are to be handled after a successful operation.

ON_ERROR: Removes all diagnostic log files.
SUMMARY_IN_BACKUP: Saves 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. The 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)

Specifies saving additional information about the files in the backup image. This information can be used by LOGICAL_RESTORE to verify the integrity of the backup image. The information for NAME, which is all the files in the backup image, is always saved.

Setting this option to HASH, which is only supported for -path backup, records a hash value for every file in the backup image.

Setting this option to SIZE records the size for every file in the backup image. Both HASH and SIZE can be specified.

-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. The default value 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-mod-tracking- flag. Backup fails if any source table is not possible to be a row modification tracking table (for example when a table is organized 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