Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.
By Kate Wheat, Laura Kunioka-Weis, and Haakon Roberts
One of the highly anticipated new features of Db2 13 is the ability to collect real-time and historical information about IBM Db2 utility executions and the objects that were processed. You can use this information to manage your utilities more effectively. For example, you can:
Find the last time a specific utility was run on an object.
Compare the performance of certain utilities over time.
Determine the objects for stopped or active utilities, including the objects currently being processed by the utility.
Check daily utility executions for failures and take immediate corrective actions.
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.
And more!
When Db2 13 is running at function level 501 or later and the new utility history feature is turned on, Db2 collects utility execution information. Starting in function level 504, you can also turn on utility object-level history. Db2 then also collects information about the objects that were processed by the utilities.
To activate utility history collection, set the new subsystem parameter UTILITY_HISTORY to UTILITY or OBJECT. OBJECT indicates that both utility history and object-level history are to be collected. UTILITY indicates that only utility history is to be collected. By default, utility information is not collected (UTILITY_HISTORY=NONE).
Once utility history collection is activated, Db2 begins storing utility information in the new Db2 catalog tables SYSIBM.SYSUTILITIES (for utility history) and SYSIBM.SYSOBJEVENTS (for object-level history).
A row is inserted into SYSUTILITIES at the beginning of each utility execution. Each row contains details about the utility execution, such as the job name, utility name, number of objects, starting and ending timestamp, final return code, elapsed time, and CPU time; the information is updated as the utility progresses and when it ends. Some of these SYSUTILITIES columns are shown in the following query output:
Notice the EVENTID column. Each row in SYSUTILITIES has an event ID which uniquely identifies that utility execution. The event ID is set by the new Db2 sequence SYSIBM.DSNSEQ_EVENTID. If rows are inserted into the SYSCOPY catalog table by the utility, the same event ID value is recorded in SYSCOPY so that the rows can be correlated between the two tables. The event ID is included in the recovery information that is reported by the REPORT utility.
Tip: The event IDs are not necessarily in sequential order. Therefore, if you want to view SYSUTILITIES rows in chronological order, use the STARTTS (start timestamp) column to order your query result.
If you activate utility object-level history collection, rows are also inserted into SYSOBJEVENTS for each object to be processed by a utility execution. An object is a table space or index space or, if the object is partitioned, a single partition. These rows include the same EVENTID value as the corresponding SYSUTILITIES row.
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 when applicable for the main function of the utility. SYSOBJEVENTS rows with some of the columns are shown in the following query output:
When utility history is active, you’ll see the following new informational message in the job output:
The level value in this message will tell you the level of utility history that is being collected: UTILITY or OBJECT. Note that OBJECT means that both utility history and object-level history is collected.
level
Successful utility execution is given priority over utility history collection. In other words, if utility history cannot be collected for any reason, utility processing is not impacted and the utility can still complete successfully. Utility history is not collected in certain situations, such as when utilities are run on utility history catalog objects or for those utility control statements that define a list of objects or control behaviors for other utilities, such as LISTDEF and OPTIONS. Object-level history is not collected for some utilities; an example is BACKUP SYSTEM which processes all objects.
After utility history collection is activated and data has been collected, you can run queries to mine that information. For example, the following query shows utilities that ended with errors in the last 24 hours:
In this example, the following query returns the SYSOBJEVENTS row for the most recent utility execution on partition 3 of table space DSN8D13A.DSN8S13E:
The SYSOBJEVENTS.EVENTID column value can then be used to query the corresponding SYSUTILITIES row for utility execution information.
You can find some more sample queries of SYSUTILITIES in the Db2 13 Redbook (IBM Db2 13 for z/OS and More).
You should regularly assess the size of SYSUTILITIES and SYSOBJEVENTS and delete information that you no longer need. While Db2 does not have a built-in feature to help with this maintenance, you can use tools, such as Db2 Administration Tool, to easily delete these rows according to your criteria.
For more information about the details of utility history and how to use it, see the following resources:
Db2 for z/OS documentation:
Monitoring Utility History SYSUTILITIES catalog table SYSOBJEVENTS catalog table UTILITY HISTORY (UTILITY_HISTORY subsystem parameter) Function level 501 Function level 504
Redbook:
Redbook: IBM Db2 13 for z/OS and More (See chapter 8)
Kate Wheat is a Senior Information Developer for Db2 for z/OS Utilities.
Laura Kunioka-Weis is a Principal Software Engineer for Db2 for z/OS Utilities development.
Haakon Roberts is a Distinguished Engineer (DE), Director of Db2 for z/OS, and CTO for IBM z Data & AI.
Copy