SQL CPU Utilization was introduced in the IBM i 7.2 release. This metric helps you discover how much of your CPU is being used by SQL operations, and is a starting point to determine if your CPU utilization is due to SQL or other work on the system. This metric is available on a per-thread basis and aggregated for the partition as a whole.
The IBM Knowledge Center describes SQL CPU Utilization as the percentage of available CPU time used to perform work done on behalf of SQL operations.
A variety of interfaces show you the SQL CPU Utilization for your partition.
- Work with System Activity (WRKSYSACT) command.
At the top of the display is a field titled Overall SQL CPU Utilization, which is the partition-wide value. You will also find an SQL CPU Util column for each job or task. You can prompt the WRKSYSACT command and select “*SQLCPU” for the sequencing order. - SYSTEM_STATUS_INFO view.
The QSYS2/SYSTEM_STATUS_INFO view includes the SQL_CPU_UTILIZTION column. While this column is always null today, I’ve included this IBM i service in the list for completeness. SQL is a powerful tool to do analysis of system information, and the SYSTEM_STATUS_INFO view includes the ability to study elapsed CPU and other performance metrics. I plan to write more about this service in a future blog - Database perspectives in the Performance Data Investigator.
In the Investigate Data task, there is the Database content package (you must have the Performance Tools product (5770-PT1) installed for these graphs to be available to you). One of the starting perspectives is SQL CPU Utilization Overview, which shows SQL CPU utilization for your partition.
Below is an example screen capture of this chart:
- Navigator System Monitors on the 7.2 release allow you to monitor CPU Utilization (SQL), which is defined as the amount of unscaled system CPU consumed performing work done on behalf of SQL operations relative to the configured CPU time (processor units) available to the partition.
- And, of course, for those of you who are knowledgeable about the Collection Services files, you will find these metrics in the files.
- The systemwide SQL CPU Utilization metrics are in the QAPMSYSTEM file in the following fields:
- SYSQLCPU—unscaled SQL CPU time used. The amount of unscaled processor time (in microseconds) spent performing work done on behalf of SQL operations.
- SYSQLSCPU—scaled SQL CPU time used. The amount of scaled processor time (in microseconds) spent performing work done on behalf of SQL operations.
- The thread-level metrics are in the QAPMJOBMI file in the following fields:
- JBSQLCLK—SQL clock time. The amount of clock time (in microseconds) this thread has spent performing work done on behalf of an SQL operation.
- JBSQLCPU—thread unscaled SQL CPU time used. The amount of unscaled processor time (in microseconds) this thread has used performing work done on behalf of an SQL operation.
- JBSQLSCPU—thread scaled SQL CPU time used. The amount of scaled processor time (in microseconds) this thread has used performing work done on behalf of an SQL operation.
- The systemwide SQL CPU Utilization metrics are in the QAPMSYSTEM file in the following fields:
(The blog Understand Scaled CPU Time discusses scaled and unscaled CPU time).
The thread-level support for SQL CPU Utilization was part of additional job-level SQL metrics added in the 7.2 release and PTF’d back to 7.1. I previously wrote about this in Job Level SQL Metrics in Collection Services and Viewing Job Level SQL Metrics with the Performance Data Investigator.
If you want another source of data to study trends and spikes of resource consumption, consider querying the detail found under QSYS2.SYSLIMITS. System Limits information is automatically maintained by the IBM i operating system and Db2 for i; I wrote a little about it in the blog Tracking IBM i System Limits Part 2. Like any good health metric, its only beneficial if you know about it and incorporate it into your Systems management strategy.
This blog post was edited to fix broken links on April 15, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.