Viewing DB2 Object Performance Data in the Investigator › Application Performance Monitoring › SQL Exceptions

SQL Exceptions

You can view exception SQL collection data on application workload and resource use. You can view:

• Data at the user level.
• Exception requests captured by plan for the user.
• Reasons that the exception request was captured.
• SQL statements issued by the request. After reviewing the SQL statements, you can edit the statement and execute it or EXPLAIN it to review the access path and recommendations for possible changes to improve performance.
• SQL text to see what is causing the exception.
• SQL statistics to understand where DB2 is spending time when executing the statement (list of dynamic SQL requests that are associated with the statement: PREPARE, DESCRIBE, OPEN, FETCH, CLOSE).

You can sort the data by averages or totals.

Review the information to determine why the exception SQL request was collected. You can now analyze the reason for collection. Review the application group and resource group to see the threshold values that were applied to the exception request. The threshold value determines why the exception SQL request was collected. You can then view the SQL call statements for the exception request and then list the SQL call statement types that make up the exception SQL request.

Detailed execution statistics are collected on both static and dynamic SQL. All major accounting trace performance information is collected at the SQL call level.

Note: To store data for exception SQL collection, you must set the Exception SQL field to Y and specify the exception SQL collection data sets in the CA Detector datastore. To include dynamic exception SQL activity, specify Y in the Dynamic SQL field. To limit the amount of activity that is captured, use collection profiles in CA Detector to customize exception SQL and SQL error collection by specifying threshold values or excluding conditions that are of no interest.