Accelerator “Statement Level Monitoring” support in OMEGAMON XE for DB2 [Part I of a two part series]
Matthias Tschaffler 060000E4RA Visits (10039)
The IBM® DB2® Analytics Accelerator offers customers an excellent way to accelerate OLAP (warehouse) queries and as such provide the base infrastructure for real time analytics. This is transparent for existing DB2 z/OS applications making the analytics accelerator the unique solution on the market.
New DB2 instrumentation for statement level monitoring
DB2 APARs PI23083 (DB2 10) and PI30005 (DB2 11) introduced accelerator statement level monitoring via IFCID 316 (dynamic statements) and IFCID 401 (static statements). These APARs have a functional dependency on the IBM DB2 Analytics Accelerator V4 or higher. Without the accelerator server support, no statement-level monitoring metric will be reported.
Understanding the new accelerator statement level monitoring instrumentation
The basic idea behind the instrumentation is that DB2 creates a new statement cache entry (static or dynamic) that represents an accelerated statement. The IFCID containing this new entry (IFCID 316 and 401) has a second repeating data section (dsect) that can be associated with the repetition in the first data section and contains the acce
When it comes to statement eligibility for acceleration, IFCIDs 316 and 401 have been further extended to add a flag that indicates the eligibility. These flags are part of dsect 1 of IFCID 316 (QW0316ELI) and IFCID 401 (QW0401ELI). This is sometimes referred to as "accelerator modeling" and the ZPARM ACCELMODEL=YES needs to be set to have this flag filled with values other than "0".
Which accelerator centric metrics are provided?
OK, now we know the motivation behind the need to have accelerator metrics and how they technically speaking are supported by DB2 z/OS.
The following picture illustrates the metrics provided in dsect 2 for IFCIDs 401 (dsect name is QW04012) and IFCID 316 (dsect name is QW03162).
Data from IFCID 316/401 dsect 1 needs to be correlated with dsect 2 to find the respective accelerator metrics. That is why it is necessary to inherit some of the data from dsect 1 in dsect 2 for the corresponding statement. For IFCID 401 this is QW0401AID, which is the statement ID from the static statement cache. As the inherited value QW0401ID is also unique from a Data Sharing Group perspective, the correlation is pretty trivial.
Besides the difference in correlating the accelerator metrics for static and dynamic statements, the actual accelerator metrics provided are the same for both static and dynamic accelerated statements:
All values are accumulated for each execution (as usual for any of the statement cache metrics).
Support for accelerator statement level monitoring and statement eligibility in IBM Tivoli OMEGAMON XE for DB2
As mentioned initially, OMEGAMON XE for DB2 has provided accelerator monitoring support from day one. With the advent of statement level monitoring and eligibility metrics from the accelerator, OMEGAMON XE for DB2 provided support in the following OMEGAMON XE for DB2 components:
1. Batch RECTRACE and Performance Database (PDB) support
As the instrumentation enhancements affect IFCID 316 and 401, the respective RECTRACE routines have been enhanced to format the new data section and eligibility flag.
The following shows an example for IFCID 401:
For the PDB support the existing IFCID 316/401 tables have been extended to consider the "eligibility" flag and new tables (DB2PMFRTRC_DYNSQL2 and DB2PMFRTRC_STASQL2) have been introduced to store the accelerator metrics and to reflect the 1:n relationship between the basic statement cache information and the associated accelerator information.
2. Performance Expert client support
The Performance Expert client views for the dynamic and static statement cache content display have been enhanced and extended to reflect the accelerator metrics.
Double click an entry to view the metrics, including the SQL statement for dynamic statements, in an overview:
You can also start your analysis for accelerated statements using the normal SQL statements view and sort the table view on the "Accelerated" column. Make sure you include the column using the "Customize Table columns" icon.
Selecting the details of the statement (double click) also shows you the accelerator metric details for each accelerator that has accelerated the query (in the following example this is only accelerator "SIM21").
You can refresh the accelerator metrics by un-checking and checking the "Receive Accelerator statement information".
To understand acceleration eligibility for your SQL workload (remember: ACCELMODEL=YES), you can select and sort on the column named "Eligible for Acceleration".
This shows statements that are eligible for acceleration. If you consider the accumulated CPU time for those eligible statements, it gives you an idea of how much DB2 CPU time could be saved if all statements are run on the accelerator. The Statement Details also show you the value of the eligibility flag:
3. Web Console Dashboard support (SQL Statements and Extended Insight)
In a similar way to Performance Expert client support, the statement acce
In this blog we introduced our DB2 support for accelerator statement level metrics. We also showed how our OMEGAMON XE for DB2 components can help DBAs and SYSPROGs evaluate accelerator performance. Please visit the Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS Knowledge Center or online help for more details, and please look out for part II where we’ll discuss monitoring support for ‘accelerator-only’ tables.