Start of change

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:

  1. Activate utility history collection by setting the UTILITY_HISTORY subsystem parameter to UTILITY or OBJECT:
    UTILITY
    Indicates that utility history is to be collected (in SYSUTILITIES).
    OBJECT
    Indicates that utility and object-level history is to be collected (in SYSUTILITIES and SYSOBJEVENTS).

    If you specify OBJECT and utility object-level history is not supported for a utility, utility history is still collected for that utility and stored in SYSUTILITIES, but no information will be stored in SYSOBJEVENTS for that utility.

    If you specify OBJECT for subsystems that are not running Db2 13 function level 504 or higher, UTILITY is used instead.

    By default, utility information is not collected (UTILITY_HISTORY=NONE).

    For data sharing, ensure that all members of the data sharing group have utility history collection activated (UTILITY_HISTORY=UTILITY or UTILITY_HISTORY=OBJECT); otherwise, the utility history will be incomplete.

    The UTILITY_HISTORY setting that is active when a utility begins execution is honored until the utility completes or is terminated. This behavior also applies to restarted utilities; a restarted utility honors the UTILITY_HISTORY setting that was active at the original execution.

    When utility history is active, message DSNU3031I is included in the utility job output. This message lists the level of utility history collection (UTILITY or OBJECT) and the event ID.

    Utility history and object-level history collection is automatically deactivated in the following situations:

    • Db2 is brought up in remote site recovery mode, tracker site mode, or system RECOVER-pending state.
    • The recovery of all objects has been deferred (DEFER ALL) during restart with Db2 in ACCESS(MAINT) mode.

    If utility history is used for audit purposes, be aware that resource-unavailable conditions on SYSUTILITIES or SYSOBJEVENTS, their table spaces (DSNDB06.SYSTSUTL and DSNDB06.SYSTSOEV), or their indexes will prevent utility history collection until the conditions are resolved. If utility history collection does not occur, informational message DSNU3032I (for utility history) or DSNU3034I (for object-level history) is issued in the utility job output. These messages do not affect utility execution and utilities can run successfully during this period. For more information stopped utilities and utility history, see Utility history restrictions and stopped utilities.

  2. Query SYSUTILITIES, SYSOBJEVENTS, and SYSCOPY as needed to retrieve utility history information.

    You can use queries to filter and order the information in these catalog tables when checking, analyzing, and comparing utility executions. You can use this information to calibrate your utility jobs for better performance. Be aware that for active or stopped utilities, the information returned by these queries reflects the current progress and might be incomplete.

    Query tips:
    • Use ISO(UR) for queries on the SYSUTILITIES, SYSOBJEVENTS, or SYSCOPY tables to avoid impacting utility processing or other processes that might be updating these tables.
    • Use the EVENTID column to correlate rows in SYSUTILITIES, SYOBJEVENTS, and SYSCOPY.
    • You can create user-defined indexes on SYSUTILITIES, SYSOBJEVENTS, and SYSCOPY as needed for query performance.
    • If utility object-level history was not collected, the SYSCOPY records might give some indication of the objects that are processed by the utility.
  3. Regularly assess the size of SYSUTILITIES and SYSOBJEVENTS and delete information that is no longer needed.

    To assess the size and growth pattern of the associated table spaces, you can use real-time statistics information in SYSIBM.SYSTABLESPACESTATS, such as the TOTALROWS, DATASIZE, and NPAGES values.

    To delete rows, use SQL DELETE statements or other Db2 tools. Any authorized user can insert, update, and delete rows in SYSUTILITIES and SYSOBJEVENTS.

    Maintenance tips:
    • When SYSUTILITIES rows are deleted, also delete the corresponding SYSOBJEVENTS rows with the same EVENTID column values. Keeping SYSOBJEVENTS rows is not practical when the corresponding SYSUTILITIES rows have been deleted, because the information about the utility execution no longer exists. Although recommended, this action is not required or enforced; no referential constraints are defined on SYSOBJEVENTS and SYSUTILITIES. You can delete rows from one of these tables without deleting the corresponding rows from the other table, if needed.
    • To keep the size of the SYSOBJEVENTS table manageable, you might want to delete the SYSOBJEVENTS rows sooner than SYSUTILITIES rows.
    • Consider deleting SYSOBJEVENTS and SYSUTILITIES rows for certain utilities sooner than rows for other utilities.
End of change