Support for monitoring SQL Data Insights accounting metrics
To monitor SQL Data Insights, Db2 has introduced new accounting instrumentation in the QWAC data section that is part of IFCID 3 and IFCID 148. While IFCID 3 is used in Batch reporting, IFCID 148 is used as part of the real-time monitoring function of OMEGAMON for DB2® Performance Expert.
BATCH reporting support
Extensions to the Batch ACCOUNTING and RECTRACE report sets have been made to track the respective SQL Data Insight metrics.
The sample SYSIN in the Batch reporting job creates an ACCOUNTING REPORT and a RECTRACE report. ACCOUNTING TRACE support is also available (not shown).
The SQL Data Insight metrics in the ACCOUNTING report are as follows (CLASS 1 / 2 times block only shown here):
The report reflects an aggregation of the respective metrics considering the respective OMEGAMON Db2 IDs like PLANNAME or AUTHID. Special attention can be given to the parallel task processing metric for SE (Specialty Engine) which is computed by the reporting function. These fields display the CPU and elapsed time spent in the AI build in functions and the number of invocations of AI functions (EN/EX-SQL DI).
In addition to the BATCH reporting functionality, the Performance Database has been extended to include the new SQL Data Insight metrics and you can process them also using the Spreadsheet Utility by generating CSV files from SMF or GTF data accordingly.
For the RECTRACE report, OMEGAMON for Db2 Performance Expert shows the following details:
Realtime monitoring support
Realtime Monitoring support for the SQL Data Insight metrics has been added to the Performance Expert Client (PE Client) interfaces as well as the Enhanced 3270 interface when zooming into Thread Details from a Thread Summary workspace.
These metrics show the amount of CPU or Specialty Engine time and SQL Data Insight Events that occur when the respective thread detail snapshot is taken. Continuing to refresh the details screen shows whether activity triggered by SQL Data Insight is performed by the respective thread.
The following screen shot shows how these metrics are displayed in the E3270 “Thread Detail Accounting” workspace (see highlighted fields):
The real-time zoom in the Thread History in E3270 has been extended to include the SQL Data Insight metrics.
Support for the PE Client is similar (see “Data Insight” labels in screenshot):
Finally, the Tivoli Enterprise Thread Detail workspace has been enhanced to include a new view Thread SDI Activity that exposes the respective metrics accordingly.