Monitoring utility history
FL 501 To help tune your Db2 utility jobs and manage your utility strategy, consider activating utility history and FL 504 utility object-level history.
When utility history collection is activated, all real-time and historical information about IBM® Db2 utility executions is stored in the Db2 catalog table SYSIBM.SYSUTILITIES. When utility object-level history collection is also activated, real time and historical information about the objects for these utility executions are stored in the Db2 catalog table SYSIBM.SYSOBJEVENTS.
You can use the information in SYSUTILITIES and SYSOBJEVENTS to perform the following tasks:
- Check daily utility executions for failures and take immediate corrective actions.
- Ensure adherence to best practices or site standards for utilities.
- Analyze and compare utility information from one execution to another.
- Check the complete history of utility executions for a given object.
- Analyze comparable utility executions.
- Use historical trends to balance the utility workload (by moving jobs to a different execution window and moving objects from one job to another) and to predict and circumvent issues.
- Track the size of objects over time.
Utility history collection is supported for the following IBM Db2 utilities:
- BACKUP SYSTEM
- CATMAINT
- CHECK DATA
- CHECK INDEX
- CHECK LOB
- COPY
- COPYTOCOPY
- LOAD
- MERGECOPY
- MODIFY RECOVERY
- MODIFY STATISTICS
- QUIESCE
- REBUILD INDEX
- RECOVER
- REORG
- REPAIR
- REPORT RECOVERY
- REPORT TABLESPACESET
- RUNSTATS
- STOSPACE
- UNLOAD
Utility object-level history collection is supported for all utilities in the preceding list, except for the following utilities:
- BACKUP SYSTEM
- CATMAINT
- REPORT RECOVERY
- REPORT TABLESPACESET
- STOSPACE
Before you begin
Depending on the level of utility history that you want to monitor, you must be running one of the following Db2 13 function levels:
- FL 501 To monitor utility history, your subsystem must be running Db2 13 function level 501 or later. When you run CATMAINT on V13R1M500 to change the catalog level to 501, the required objects for utilities history (the catalog table space DSNDB06.SYSTSUTL and its indexes) are created for you. These objects are created with the DEFINE NO attribute; the first insert into the SYSUTILITIES table defines these objects.
- FL 504 To monitor utility object-level history, your subsystem must be running Db2 13 function level 504 or later. When you run CATMAINT to change the catalog level to 504, the required objects for utility object-level history (the catalog table space DSNDB06.SYSTSOEV and its indexes) are created for you. These objects are created with the DEFINE NO attribute; the first insert into the SYSOBJEVENTS table defines these objects.
About this task
When you activate utility history collection, a row is inserted into SYSUTILITIES at the beginning of each utility execution. This row includes a unique event ID in the EVENTID column to identify the utility execution. If the utility execution inserts one or more rows into SYSIBM.SYSCOPY, the EVENTID value is also recorded in the EVENTID column in SYSCOPY. Information in the SYSUTILITIES row is updated as the utility progresses. Each row contains details about the utility execution, such as the job name, utility name, number of objects, starting and ending timestamp, elapsed time, CPU time, zIIP time, final return code, and special conditions. For detailed information about how information is updated in SYSUTILITES, see Utility history information in SYSUTILITIES. For a list of all SYSUTILITIES columns and their descriptions, see SYSUTILITIES catalog table.
When you activate utility object-level history collection, a row is inserted into SYSOBJEVENTS for each object processed by a utility execution. (An object is a table space or index space and if partitioned, then a row is inserted for each partition.) This row includes the same EVENTID value that is in SYSUTILITIES and SYSCOPY for the same utility execution. Information in the SYSOBJEVENTS row is updated as the utility progresses. The information includes the timestamp when processing of the object began and the number of rows, keys, pages, LOBs, or records processed, which helps you determine whether an object is waiting to be processed, is in progress, or has completed processing. For detailed information about how information is updated in SYSOBJEVENTS, see Utility object-level history information in SYSOBJEVENTS. For a list of all SYSOBJEVENTS columns and their descriptions, see SYSOBJEVENTS catalog table.
For more information about restrictions when collecting utility history, including how stopped utilities are handled, see Utility history restrictions and stopped utilities.
Procedure
To monitor utility history: