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?
There is a difference between:
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
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.