LOGICAL_RESTORE stored procedure - to restore either an entire schema or tables in a schema from a logical backup image

Use the Db2 logical schema restore feature to fully restore a schema or one or more tables within a schema.

Authorization

The following authorities are required to run the LOGICAL_RESTORE stored procedure on a Db2® schema:
  • EXECUTE authority on the SYSPROC.LOGICAL_RESTORE stored procedure. (The default setting is NOT granted to PUBLIC)
  • SCHEMAADM authority on the schema, if a schema is being restored with the drop-existing option activated.
  • One of the following to drop or create tables in a schema:
    • SCHEMAADM authority on the schema
    • DROPIN and CREATEIN authority on the schema
  • INSERTIN authority to populate table data
  • ALTERIN, DELETEIN, SELECTIN authority on the schema, if you are restoring an incremental or delta backup image
  • 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 permission on the following procedures:
    • SYSPROC.ADMIN_DROP_SCHEMA
    • SYSPROC.WLM_SET_CLIENT_INFO
    • SYSPROC.MONITOR_UTIL
    • SYSPROC.ADMIN_SET_MAINT_MODE

Scope

The SYSPROC.LOGICAL_RESTORE() re-creates objects and populates tables with data from the backup image. It is possible to restore only a subset of tables.

Syntax

A schema-level restore operation can be performed by passing the schema name to the LOGICAL_RESTORE stored procedure by using -schema <schema_name> option. To restore one or more tables, use the -table option.

Read syntax diagramSkip visual syntax diagramLOGICAL_RESTORE(' -typeFULLINCDEL-timestamp <image timestamp>-tsm-pathlist-of-path(s)remote-storage-s3s3-options-coscos-options-schema <schema-name>-target-schema name-table <table-name> | <table-name-1> <table-name-2> ...<table-name-x>-drop-existing-errorlogdir <path>-keeplogsON_ERRORALL_IN_ERRORLOGDIR-sessions <N>-image-checkNONEHASHSIZENAMEONLY-media-connection-timeout <milliseconds> -keep-rcac-replace-rcac -unlockschema-cleanup-failed-restore-enable-row-mod-trackingdelete-backup')
s3-options
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>
cos-options
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>

Command parameters

-type FULL | INC | DEL
FULL
Full backup
INC
Cumulative incremental backup
DEL
Delta incremental backup, also known as a differential backup.
-schema <schema-name>
Schema from the backup image to be restored. Only one schema name is allowed.
-path
Specifies a list-of-path(s) or remote-storage for where the backup image is stored. 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 extbl_strict_io and extbl_location configuration parameters. The backup directory and files are owned by the user that invoked the logical backup.
remote-storage
A string starting with the keyword DB2REMOTE can be used to specify a remote storage alias. For more information on defining a DB2REMOTE alias, see CATALOG STORAGE ACCESS. Only one DB2REMOTE string can be specified using -path option.
Important: The general syntax for using the remote storage alias is 
DB2REMOTE://<alias>/<container>/<object>
where <object> is any string representing the name of the object, or file, on the remote storage. For the LOGICAL_RESTORE() stored procedure, the name of the object string must be DB2. If no name is specified, the value defaults to DB2.
-tsm
Backup image is to be stored in an IBM Spectrum Protect server. Mutually exclusive with path and s3 options.
-s3
Backup image is to be stored in the 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 the 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 the 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 | ALL_IN_ERRORLOGDIR}
Indicates how diagnostic log files are to be handled after a successful operation.
ON_ERROR
Removes all diagnostic log files. This is the default setting.
ALL_IN_ERRORLOGDIR
Preserves all diagnostic log files in the specified errorlogdir location.
Note: On failure, all diagnostic log files are preserved irrespective of the value of the -keeplogs option.
-sessions <N> (optional)
Spawns N threads/connections to restore individual tables concurrently. The default value is 4 per schema.
-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.
-delete-backup
Permanently remove backup image. Requires path/media and timestamp to be provided. Cannot be specified with -type or -schema
-table <table-name> | <table-name-1> <table-name-2> ...<table-name-x> (optional)
One or more tables to be restored. Table must not exist, or a -drop-existing option must be specified.
table-name
Name of one or more tables to be restored. Multiple table names must be separated by space.
-target-schema name (optional)
Restore backup image using different schema name without dropping the original schema. -drop-existing affects new schema name.
-drop-existing (optional)
Drop the existing schema and its table/non-table objects before restore. If used in conjunction with -table option, only affected table is dropped.
-keep-rcac
Restore RCAC-enabled tables, masks, and permissions without dropping them.
-replace-rcac
Restore RCAC-enabled tables, masks, and permissions by dropping and re-creating them.
-enable-row-mod-tracking (optional)
Restores a backup image of a schema that was taken by using the -backup-migrate option. With -enable-row-mod-tracking option, the schema is restored as row modification tracking although the source schema was not enabled for row modification tracking. If used with -target-schema option, then the target schema is restored as row modification tracking.
-image-check [HASH, SIZE, NAME, NONE, ONLY] (optional)
Specifies verification checks for the integrity of the backup image. By default, all options that are specified during backup are enabled (including implicit NAME). You can use this option to limit the verification to only the specified checks. Options not collected during backup will be silently ignored.
HASH
Computes and records hash values for all files in backup image. Only allowed for -path backups.
SIZE
records sizes of all files in backup image.
NAME
Identifies missing or unexpected file names.
NONE
Prevents the running of integrity checks.
ONLY
Runs integrity checks only, without invoking a restore operation. Validates a backup image.
-unlockschema (optional)
Unlock schema after a failed restore. Does not complete previously failed operation.
-cleanup-failed-restore (optional)
Cleanup after a failed restore. Drops all objects and the schema itself. In case schema cannot be dropped it is still unlocked.
-timestamp <image timestamp>
Timestamp the label of the backup image to restore.

New sqlcodes

The following example shows an SQL1797N message that is returned when a LOGICAL_RESTORE() operation fails:
SQL1797N The "SYSPROC.LOGICAL_RESTORE" utility has failed with error 
"Valid path required". SQLSTATE=5UA0Q

Usage notes

Some non-table database objects (such as functions, procedures, views, materialized query tables, and variables) might not be created as part of logical restore operation due to tolerable conditions. In such situation, a warning is written to the tracelog during the restore operation.