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

Use the Db2 logical 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-path list-of-path(s)-s3s3-options-coscos-options-schema <schema-name>-target-schema name-table <table name>-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)
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>)

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 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.
-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 | all_in_errorlogdir}
Indicates how diagnostic log files are to be handled after a successful operation.
on_error - remove all diagnostic log files.
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.
-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.
-delete-backup
Permanently remove backup image. Requires path/media and timestamp to be provided. Cannot be specified in conjunction with -type or -schema
-table <table> (optional)
Table to be restored. Table must not exist, or -drop-existing option must be specified.
-target-schema name (optional)
Restore backup image using different schema name without dropping original schema. -drop-existing affects new schema name.
-drop-existing (optional)
Drop 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 recreating them.
-enable-row-mod-tracking (optional)
Restores a backup image of a schema that was taken by using the -backup-migrate option. This option does not enable row modification tracking. Also, enables restore target schema for row modification tracking.
image-check [HASH, SIZE, NAME, NONE, ONLY (optional)

For backup: 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 is on by default. Information to verify NAME option (list of names of all files in the backup image) is always collected.

For restore: validate schema backup image prior to restore. By default all options specified during backup are enabled (including implicit NAME). Only specified options will be validated. Validation for options not collected during backup will be silently ignored.

  • HASH - compute and record hash values for all files in backup image. Only allowed for `-path` backups.
  • SIZE - record sizes of all files in backup image.
  • NAME - identify missing or unexpected file names.
  • NONE - do not perform any integrity checks.
  • ONLY - perform integrity checks only without invoking restore (validate 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 schema itself. In case schema cannot be dropped it is still unlocked.
-timestamp <image timestamp>
Timestamp 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.