Monitoring SQL statements in current activity

Review these recommendations for monitoring current SQL activity.

For an SQL statement to appear in current activity:
  • Db2 Query Monitor must be active before the application calls the call attach facility to process the SQL call. Db2 Query Monitor must see the start of a particular SQL call in order for it to be tracked. For example, if Db2 Query Monitor is started in the middle of a long running static SELECT statement, Db2 Query Monitor did not record the starting metric values of the SQL statement and cannot report on its execution.
  • The SQL statement must be identified by the current monitoring profile. If a statement is not identified by the current monitoring profile, or if it is excluded by the current monitoring profile, information about the activity will not appear in current activity.

  • Either one or more SQL calls in the statement must have completed or one of the calls that comprise the SQL statement must have been executing within Db2 for a minimum of five seconds (wall clock time). For a static cursor comprised of an OPEN, FETCH, CLOSE sequence, the statement appears in current activity after the OPEN finishes execution or after the OPEN call has been in Db2 for over a period of five seconds.
    Note: For Db2 Query Monitor to be able to see SQL calls that have been executing within Db2 for a period of five seconds, the Query Monitor Subsystem must be receiving adequate CPU service in order for this tracking work to execute.

While an SQL statement executes, Db2 Query Monitor evaluates the statement for alerts and determines whether or not to queue the statement for processing by the CAE Agent. After the CLOSE has executed, Db2 Query Monitor considers the SQL statement to have completed and evaluates the statement for exceptions using current monitoring profile. When Db2 Query Monitor determines that the statement has completed, Db2 Query Monitor removes the statement from current activity.

Db2 Query Monitor records information about current activity and exceptions on a statement basis. For example, if a particular thread opens two cursors, each individual cursor (statement) will be traced as a separate line item in the current activity and exception displays even though they have been assigned the same thread token. This allows Db2 Query Monitor to keep track of and report on the individual components used in diagnosing long-running or problematic statements (for example, host variables SQL text, SQLCA).

Other considerations:

  • Db2 Query Monitor's current activity feature is not a thread display. If a thread is not currently involved in the execution of an SQL statement, no metrics for that thread appear in current activity.
  • Db2 Query Monitor only gathers dynamic SQL text for PREPARE and EXECUTE IMMEDIATE SQL calls. If a dynamic SQL statement comprised of a PREPARE, OPEN, FETCH, CLOSE call sequence, Db2 Query Monitor will gather the SQL text only for the PREPARE call. If Db2 Query Monitor is inactive and only records activity for the OPEN, FETCH, and CLOSE calls, the SQL text will not be gathered and when the user attempts the view the SQL text, message CQM196I will be generated.
  • Db2 Query Monitor has the capability of reporting SQL call execution prior to the termination of the call. Exceptions and alerts are noted in the current activity displays if the thresholds defined in the monitoring profile are exceeded, however the statement execution will not be externalized to the Performance History Files until the statement execution has ended and if the exception limit defined in the monitoring profile has not been exceeded.