Detector Total Activity Summary

Products

• Bind Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS
• SYSVIEW Performance Management Option for DB2 for z/OS
• Plan Analyzer for DB2 for z/OS
• Subsystem Analyzer for DB2 for z/OS

Issue/Introduction

An  application was getting a poor response for a particular query. Detector shows  the Activity log with the following results:

Total/Avg ==> T              DB2 SSID  ==> DI1C           Planname ==> ?RRSAF  

Interval Date => 08-02-17   Interval Time => 10:00:00    Elapsed Time => 01:00                                                                                

 SQL_TEXT         -> SELECT DISTINCT t0.REFD_PRSN_UID, t0.REFD_RSN_UID, t0.LU_T                                                                              

SQL Statement Information                                                      

 SQL_CALL         -> PREPARE             STMT#            -> 0000001           

 SECT#            -> 00001               USE_COUNT        -> 31                

 SQL              -> 45                  TIMEPCT          -> .05%              

 CPUPCT           -> .14%                INDB2_TIME       -> 00:00.016661      

 INDB2_CPU        -> 00:00.013033                                                                                                                            

Buffer Manager Activity                                                        

 GETPAGE          -> 87                  GETPFAIL         -> 0                 

 SYNCREAD         -> 1                   SPFETCH          -> 0                 

 LPFETCH          -> 0                   DYNPFETCH        -> 0                 

 PFPAGES          -> 0                   PAGEUPDT         -> 43                

 IMWRITE          -> 0                   REOPT            -> 0                 

This looked like it wasn’t really a problem but the same query showed up in the exceptions, and was not included in the summary that I looked at. 

 Why isn’t the data for the exceptions included in the summary for the activity for the query? 

 

 

Environment

Release:
Component: PDT

Resolution

There is a difference between:

Exception data 

The exception SQL collection facilities let you evaluate and control SQL from any application origin, including SQL Client-server database gateways, Fourth-generation tools,  Typical on-demand queries.

Exception SQL collection uses collection profile information that lets you: 1-Specify when a request is considered for data capture. Exception SQL data capture provides all major accounting information for the execution of the initial request and for the SQL calls executed as a result of the request.2- Specify thresholds that when met, cause CA Detector to set a -905 SQL error code when an SQL request completes.

View current and historical exception SQL activity. This data helps you easily identify which users are executing exception SQL within the DB2 subsystem you are viewing

 AND 

Standard activity:

The CA Detector standard collection displays provide a unique, informative, and cost effective approach to understanding DB2 application use and resource consumption.

You can view accounting data values from application, plan name, package/DBRM, and SQL statement perspectives without resource-intensive DB2 performance traces. Instead, we use DB2 accounting data as a source of collection data.