Late last year, IBM enabled new support to collect SQL metrics in Collection Services. I’ll be covering this new capability in two blog articles:
- This blog will review the new metrics that are now collected and describe what you need to do to enable the collection of these additional metrics.
- A subsequent blog will review the support added to the Performance Data Investigator to allow you to view and analyze these metrics.
Collection Services collects a wealth of information, but metrics related to SQL operations were limited. The tools used for Db2 and SQL performance analysis were separate from the system performance tools; interfaces such as the Db2 Health Center, SQL Plan Cache and Index Advisor, among others, are what you need to use to understand database performance issues.
The most recent PTFs augment the tools already available for SQL performance analysis and there are now SQL metrics collected at the job level. This allows you to begin your performance analysis with Collection Services and determine if SQL operations are a contributing factor. You will still use the Db2-specific tools for digging into the details, but you now have insight into the SQL operations from Collection Services data.
This enhancement is available on IBM i 7.1 and later. For 7.1, it requires that you have installed the latest Db2 Group PTF SF99701, as well as SI47594, which provides the Collection Services enablement. It’s also a good idea to ensure that you have the latest Performance Tools Group PTF, SF99145, installed as well.
Just installing the required PTFs is not sufficient. There are activation instructions you must take to enable the collection of these metrics documented in the SI47594 or SI48100 PTF cover letters. The instructions are rather lengthy but not difficult to follow. Complete details can be found in the Database Perspectives article.
The reason for these special instructions has to do with the fact that the file, QAPMJOBMI, needs to be updated to a new level to support these additional metrics. Specifically, an updated template file for QAPMJOBMI is shipped in QSYS with the additional fields. This template file will only be used when creating a new QAPMJOBMI file in libraries where it doesn’t already exist. Existing collection libraries (for example, QPFRDATA) that contain a prior version of QAPMJOBMI will not be affected by the PTF.
Once you have the required PTFs installed and completed the necessary activation instructions, you will have SQL metrics in your job-level Collection Services data. The Performance Data Investigator can then be used to view and analyze these metrics. As I mentioned earlier, I’ll write more about the PDI support in a future blog. To be prepared to use this new capability, review the blog Navigator for i Enhancements and install the PTFs as documented in that blog.
The following are the new metrics now available for collection:
The following SQL performance metrics are collected as part of the *JOBMI category:
- SQL clock time (total time in SQ and below) per thread (microseconds)
The amount of clock time (in microseconds) this thread has spent performing work done on behalf of an SQL operation. - SQL unscaled CPU per thread (microseconds)
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. - SQL scaled CPU per thread (microseconds)
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. - SQL synchronous database reads per thread
Total number of physical synchronous database read operations done on behalf of an SQL operation. - SQL synchronous non-database reads per thread
Total number of physical synchronous nondatabase read operations done on behalf of an SQL operation. - SQL synchronous database writes per thread
Total number of physical synchronous database write operations done on behalf of an SQL operation. - SQL synchronous non-database writes per thread
Total number of physical synchronous nondatabase write operations done on behalf of an SQL operation. - SQL asynchronous database reads per thread
Total number of physical asynchronous database read operations done on behalf of an SQL operation. - SQL asynchronous non-database reads per thread
Total number of physical asynchronous nondatabase read operations done on behalf of an SQL operation. - SQL asynchronous database writes per thread
Total number of physical asynchronous database write operations done on behalf of an SQL operation. - SQL asynchronous non-database writes per thread
Total number of physical asynchronous nondatabase write operations done on behalf of an SQL operation. - Number of high Level SQL Statement count per thread
The number of high level SQL statements that executed during the Collection Services time interval. This count includes only initial invocation, independent SQL statements. It does not include dependent SQL statements invoked from within another SQL statement. This count also includes initial invocation, independent SQL statements which failed to execute successfully.
This blog post was edited to correct broken links on March 17, 2020.
This blog post was originally published on IBMSystemsMag.com and is reproduced here by permission of IBM Systems Media.