Monitoring logical schema backup and restore operations

You can monitor the progress of logical schema backup and restore operations when using the stored procedures to capture and restoring Db2 table-level data.

Logical backup and restore utilities register themselves with both the MON_GET_UTILITY table function (for in progress monitoring) and change history event monitors (for historical data). Connections that are established by either utility call the SYSPROC.WLM_SET_CLIENT_INFO stored procedure, so that workloads can be assigned and applications monitored. The following values are used as input parameters to the stored procedure call:
  • client_userid => <instance owner>
  • client_wrkstnname => <coordinator (connected) hostname>
  • client_applname => <backup name>:<schema name>:<instance name>
    For example, 
    db_backup:schema:PROD
    or 
    db_restore:table:PROD.JRNL
  • client_acctstr => <the chosen backup image timestamp (backup) or provided image timestamp (restore)>
  • client_workload => AUTOMATIC
Monitoring with the MON_GET_UTILITY table function
For the MON_GET_UTILITY table function, the UTILITY_TYPE monitor element values are set to LOGICAL_BACKUP and LOGICAL_RESTORE respectively. The values that are returned for the UTILITY_DETAIL monitor element are presented as key/value pairs that use the following format:
KEY1::VALUE1;;KEY2::VALUE2;;…;;KEYN::VALUEN;;

Values are continuously updated with the most recent data. When a logical backup or restore operation is completed, nothing is reported by the MON_GET_UTILITY table function. For a complete list of potential keys, see the Table 2 and Table 4.

Monitoring with change history event monitors
Change history event monitoring is captured if the corresponding event monitor is enabled. The following events are generated by either backup or restore operations: The values that are returned for the UTILITY_DETAIL monitor element are presented as key/value pairs: 
KEY1::VALUE1;;KEY2::VALUE2;;…;;KEYN::VALUEN;;

Values are only populated when an event is generated and are not updated. Events recorded in the table persist after a backup or restore is completed. For a complete list of potential keys see Table 1 and Table 3.

If logical restore is called with -image-check only option, then utility is does not generate change event history events and is not registered with MON_GET_UTILITY table function.

Table 1. Key/value pairs returned in the UTILITY_DETAILS field for a logical schema BACKUP START event
KEY Value Comments
SCHEMA <schema name> Always included.
TYPE FULL | INC | DEL Always included.
LOCATION <path> | TSM Always included.
SCOPE SCHEMA Always included.
TIMESTAMP <timestamp> Generated at the start of the backup operation.
COMPRESSION GZIP | LZ4| NO Always included.
FORMAT BINARY | TEXT Always included.
SESSIONS <number> Has explicit number even if no sessions are specified (default).
TABLES_TO_BACKUP_COUNT <number> Constant, does not change for the whole duration of the backup operation.
Table 2. Key/value pairs returned in the UTILITY_DETAILS field for a logical schema BACKUP PROGRESS event
KEY Value Comments
TABLES_DONE_COUNT <number> Updated for every table processed
TABLES_IN PROGRESS 'T1', 'T2', …​ , 'TX' Updated for every table processed
TABLES_IN_PROGRESS_COUNT <number> Expect to match SESSIONS except for small timing windows and at the end of the backup
TABLES_LEFT_COUNT <number> Updated for every table processed
BACKUP_PHASE PRE_BACKUP_CHECKS |BACKUP_SCHEMA_DDL |UNLOAD_TABLE_DATA |POST_BACKUP_ACTIONS  
PRE_BACKUP_CHECKS_START <timestamp> Not present if not started yet
BACKUP_SCHEMA_DDL_START <timestamp> Not present if not started yet
UNLOAD_TABLE_DATA_START <timestamp> Not present if not started yet
POST_BACKUP_ACTIONS_START <timestamp> Not present if not started yet
Table 3. Key/value pairs returned in the UTILITY_DETAILS field for a logical schema RESTORE START event
KEY Value Comments
SOURCE_SCHEMA <schema name> Always included
TARGET_SCHEMA <schema name> Reported always, equal to source_schema if no -target-schema is specified
TYPE FULL | INC | DEL Always included
LOCATION <path> | TSM | S3 Always included
SCOPE SCHEMA Always included
SESSIONS <number> Will have explicit number even if no sessions specified (default)
TABLES_TO_RESTORE_COUNT <number> Constant, does not change for the whole duration of restore
TABLES_TO_RESTORE_LIST 'T1', 'T2', …​ , 'TX' Only for table level restore
IMAGE_CHECK List of: HASH | SIZE | ROWCOUNT | NAME | NONE Can be multiple from the list.
BACKUP_IMAGE_TO_RESTORE_LIST {full: <timestamp>, inc: <timestamp>, del:<timestamp>}  
BACKUP_IMAGES_TO_RESTORE_COUNT <number>  
Table 4. Key/value pairs returned in the UTILITY_DETAILS field for a logical schema RESTORE PROGRESS event
KEY Value Comments
CURRENT_IMAGE_TABLES_DONE_COUNT <number> Updated for every table processed
CURRENT_IMAGE_TABLES_IN_PROGRESS 'T1', 'T2', …​ , 'TX' Updated for every table processed
CURRENT_IMAGE_TABLES_IN_PROGRESS_COUNT <number> Expect to match SESSIONS except for small timing windows and at the end of the restore
CURRENT_IMAGE_TIMESTAMP <type>:<timestamp> For example, full:20200622142701
CURRENT_IMAGE_TABLES_LEFT_COUNT <number> Updated for every table processed
CURRENT_IMAGE_RESTORE_PHASE PRE_RESTORE_CHECKS | PRE_RESTORE_DDL | CURRENT_IMAGE_LOAD_TABLE_DATA | POST_RESTORE_DDL | POST_RESTORE_ACTIONS1 PRE_RESTORE_DDL is only reported for FULL image restore. LOAD_TABLE_DATA includes DELETE/DDL/INSERT if INC/DEL image
IMAGE_<timestamp>_START <timestamp> Reported for every image processed. So ONL+INC+DEL+DEL may have 4 IMAGE_X_START entries
PRE_RESTORE_CHECKS_START <timestamp> Not present if not started yet
PRE_RESTORE_DDL_START <timestamp> Not present if not started yet
CURRENT_IMAGE_LOAD_TABLE_DATA_START <timestamp> Updated for every backup image
POST_RESTORE_DDL_START <timestamp> Not present if not started yet
POST_RESTORE_ACTIONS_START <timestamp> Not present if not started yet

Notes

  • 1

    The CURRENT_IMAGE_RESTORE_PHASE key presents the following progress sequences when restoring an INC + DEL image:

    • (done once) PRE_RESTORE_CHECKS →
    • (start FULL restore) PRE_RESTORE_DDL → CURRENT_IMAGE_LOAD_TABLE_DATA →
    • (start INC restore) CURRENT_IMAGE_LOAD_TABLE_DATA →
    • (start DEL restore) → CURRENT_IMAGE_LOAD_TABLE_DATA →
    • (done once) POST_RESTORE_DDL → POST_RESTORE_ACTIONS
  • For multi-schema backups, each schema is reported separately but has the same timestamp.