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.

Figure 1. Sample SYSIN DD

ACCOUNTING           
  REPORT             
    LAYOUT(LONG)     
RECTRACE             
   TRACE LEVEL(LONG) 
EXEC

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):

Figure 2. SQL Data Insight metrics in ACCOUNTING report

AVERAGE       APPL(CL.1)  DB2 (CL.2)
------------  ----------  ----------
ELAPSED TIME    1.340014    1.337055
 NONNESTED      1.340014    1.337055
 STORED PROC    0.000000    0.000000
 UDF            0.000000    0.000000
 TRIGGER        0.000000    0.000000
                                    
CP CPU TIME     2.633505    2.632807
 AGENT          0.698717    0.698019
  NONNESTED     0.698717    0.698019
  STORED PRC    0.000000    0.000000
  UDF           0.000000    0.000000
  TRIGGER       0.000000    0.000000
  SQL DI             N/A    0.000000
 PAR.TASKS      1.934789    1.934789
  SQL DI             N/A    1.648177
                                    
SE CPU TIME     1.920307    1.920295
 NONNESTED      0.000000    0.000000
 STORED PROC    0.000000    0.000000
 UDF            0.000000    0.000000
 TRIGGER        0.000000    0.000000
 SQL DI              N/A    0.000000
                                    
 PAR.TASKS      1.920307    1.920295
  SQL DI             N/A    1.569330
                                    
SUSPEND TIME    0.000000    0.667829
 AGENT               N/A    0.638205
 PAR.TASKS           N/A    0.029625
 STORED PROC    0.000000         N/A
 UDF            0.000000         N/A
                                    
NOT ACCOUNT.         N/A         N/C
DB2 ENT/EXIT         N/A       46.00
EN/EX-STPROC         N/A        0.00
EN/EX-UDF            N/A        0.00
EN/EX-SQL DI         N/A  1371968.00
DCAPT.DESCR.         N/A         N/A
LOG EXTRACT.         N/A         N/A

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:

Figure 3. RETRACE report details

|CLASS 2 DB2 ELAPSED TIME           0.215507   DB2 ENTRY/EXIT EVENTS                    2 
|        TCB TIME                          0.029649   NON-ZERO CLASS 2                       YES 
|        STORED PROC ELAPSED TIME          0.000000   CLASS 2 DATA COLLECTED                 YES 
|        STORED PROCEDURE TCB TIME         0.000000   STORED PROC. ENTRY/EXITS                 0 
|        UDF ELAPSED TIME                  0.000000   UDF SQL ENTRY/EXITS EVENTS               0 
|        CP CPU TIME UDF                   0.000000   SE CPU TIME                       0.042903 
|        TRIG ELAP TIME UNDER ENCLAVE      0.000000   SE ELIGIBLE CP CPU TIME           0.000000 
|        TRIG TCB TIME UNDER ENCLAVE       0.000000   QWACTRTT_ZIIP                     0.000000 
|        TRIG ELAP TIME NOT UNDER ENCLAVE  0.000000   ELAPSED TIME ELIGIBLE FOR ACCEL   0.000000 
|        TRIG TCB TIME NOT UNDER ENCLAVE   0.000000   CP CPU TIME ELIGIBLE FOR ACCEL    0.000000 
|                                                     SE CPU TIME ELIGIBLE FOR ACCEL    0.000000 
|SQL DATA INSIGHTS ELAPSED TIME         0.174283   SQL DATA INSIGHTS CPU TIME        0.025273 
|   SQL DATA INSIGHTS ENTRY/EXITS             19944   SQL DATA INSIGHTS ZIIP TIME       0.034893

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):

Figure 4. Db2 Thread Detail Accounting (KDPTHDA2)

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.