LOGICAL_BACKUP_DETAILS_TAB table function - for generating detail information of the logical backup images stored on a specified media
The LOGICAL_BACKUP_DETAILS_TAB table function returns details of the logical backup images on a specified path or media. The details include timestamps for the backup images and the schemas and tables contained in these backups.
Syntax
Authorization
The EXECUTE authority. By default, this authority is granted to PUBLIC.
Backup images created by using the -path option are owned by the connected user. This includes the top folder, all sub-folders, and all files.
Table function parameters
-
-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 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.
- 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 the -path option.
-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: Removes all diagnostic log files.
- all_in_errorlogir: Preserves all diagnostic log files in errorlogidr (default for -path).
Note: On failure all diagnostic log files are preserved irrespective of the value of -keeplogs option.
-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.
Usage notes
- On failure of LOGICAL_BACKUP_DETAILS_TAB(), a SQL1797N message is returned. The following example shows a SQL1797N message:
SQL1797N The "SYSPROC.LOGICAL_BACKUP_DETAILS_TAB" utility has failed with error "Valid path required". SQLSTATE=5UA0Q
Information returned
For each combination of backup image timestamp, schema-name, and table-name, the table function
returns one row. For example, if the backup image has three schemas LBAR1, LBAR2, and LBAR3, with
LBAR1 having 2 tables, LBAR2 having 4 tables, and LBAR3 having 10 tables, then the table function
returns 16 rows (2 + 4 + 10) for this timestamp.
Note: If an empty schema exists in a backup image,
the LOGICAL_BACKUP_DETAILS_TAB table function returns one row for the schema, with the TABLE column
set to NULL.
Table 1 shows the fields that are
returned by running the LOGICAL_BACKUP_DETAILS_TAB table function and describes each value.
| Field | Data type | Null/Not Null | Description |
|---|---|---|---|
| TIMESTAMP | CHAR(14) | NOT NULL | The timestamp of the backup image |
| TYPE | CHAR(1) | NOT NULL | The backup type of the image. Possible values are:
|
| PREV_TIMESTAMP | CHAR(14) | NULL | For incremental and delta backups, this is the timestamp of the previous backup image. A full backup does not have any previous backup image, so the value of PREV_TIMESTAMP is NULL. |
| INSTANCE | VARCHAR(128) | NULL | The name of the instance in the backup image. For any backup image taken of a Db2 release earlier than 12.1,this column is NULL. |
| DATABASE | VARCHAR(128) | NULL | The name of the database in the backup image. For any backup image taken of a Db2 release earlier than 12.1,this column is NULL. |
| TENANT | VARCHAR(128) | NOT NULL | The name of the tenant for the schema |
| SCHEMA | VARCHAR(128) | NOT NULL | The name of the schema in the backup image. |
| TABLE | VARCHAR(128) | NULL | The name of a table in the backup image. |
| TABLE_BACKUP_SIZE | BIGINT | NULL | Reserved for future use. |
| COMMENTS | VARCHAR(16384) | NULL | Reserved for future use. |
Examples
The following example shows the command syntax and output from running the table function in a
SELECT statement, to list information on backup images residing on S3 remote
storage:
db2 "select TIMESTAMP, TYPE, PREV_TIMESTAMP, CAST(TENANT as VARCHAR(20)) TENANT, CAST(SCHEMA
as VARCHAR(20)) SCHEMA, CAST(TABLE as VARCHAR(20)) TABLE, TABLE_BACKUP_SIZE, CAST(COMMENTS
as VARCHAR(10)) COMMENTS from table(sysproc.logical_backup_details_tab('-s3 -access-key myusername
-secret-key mypassword -default-region us-east-2 -bucket-url mybucketname
-endpoint s3.us-east-2.amazonaws.com')) fetch first 20 rows only"
TIMESTAMP TYPE PREV_TIMESTAMP TENANT SCHEMA TABLE TABLE_BACKUP_SIZE COMMENTS
-------------------- ---- -------------------- -------------------- -------------------- -------------------- -------------------- ----------
20240319111417 F - SYSTEM LBAR2 TABLE1 - -
20240319111417 F - SYSTEM LBAR2 TABLE2 - -
20240319111417 F - SYSTEM LBAR2 TABLE3 - -
20240317171458 F - SYSTEM LBAR1 TABLE1 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE1 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE2 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE3 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE4 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE5 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE6 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE7 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE8 - -
20240319111629 I 20240319111417 SYSTEM LBAR1 TABLE9 - -
20240321033314 F - SYSTEM LBAR1 TABLE1 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE1 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE10 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE11 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE12 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE2 - -
20240319112014 D 20240319111846 SYSTEM LBAR1 TABLE3 - -
20 record(s) selected.db2 "select TIMESTAMP, TYPE, PREV_TIMESTAMP, CAST(TENANT as VARCHAR(20)) TENANT, CAST(SCHEMA as VARCHAR(20)) SCHEMA, CAST(TABLE as VARCHAR(20)) TABLE, TABLE_BACKUP_SIZE, CAST(COMMENTS as VARCHAR(10)) COMMENTS from table(sysproc.logical_backup_details_tab('-path /home/afarhadi/mybackups'))"
TIMESTAMP TYPE PREV_TIMESTAMP TENANT SCHEMA TABLE TABLE_BACKUP_SIZE COMMENTS
-------------------- ---- -------------------- -------------------- -------------------- -------------------- -------------------- ----------
20240409071657 I 20240409071649 SYSTEM LBAR TABLE2 - -
20240409071649 F - SYSTEM LBAR TABLE1 - -
20240409071649 F - SYSTEM LBAR TABLE2 - -
20240409071709 D 20240409071657 SYSTEM LBAR TABLE2 - -
20240409071709 D 20240409071657 SYSTEM LBAR TABLE3 - -
20240409071709 D 20240409071657 SYSTEM LBAR TABLE4 - -
6 record(s) selected.