Optimizing responses for parallel statement alerts

Follow these steps to optimize the number of responses you see to parallel statement alerts.

About this task

When Db2 activates parallelism for certain SQL statements, several threads are started to evaluate the query results. Db2 Query Monitor monitors each of these thread as individual SQL statements and generates an alert on the message board for each thread that exceeds the alert threshold. As result, one parallel SQL statement might produce multiple alerts on the message board.

If you activate a response for such an SQL statement and select the option The repetition count of an event increments, then a response is triggered every time a thread exceeds the alert thresholds. To avoid too many responses being triggered, Db2 Query Monitor suppresses responses for most child threads. Db2 Query Monitor triggers responses only for parent threads and the first child threads.

Procedure

  1. Select the Parallel Threads optional alert key. For more information, see Optional alert keys.
  2. Add a response and specify appropriate scope that limits the number of responses you see for parallel statement alerts.
    1. In the Response Applies To field, select Event Scopes.
    2. Select the appropriate scope to limit the number of responses you see for parallel statement alerts. Expand the tree to show the Non Extra Threads scopes and check the box for the scope you want to use:
      Non Extra Threads
      Includes all Db2SqlEvent events for non-parallel SQL statement threads, parent threads of parallel SQL statements, or first-child threads that exceed the alert monitoring threshold.
      Get Page Count Exceeded Problem for Non Extra Threads
      Includes all GetPageCountExceededProblem events for non-parallel SQL statement threads, parent threads of parallel SQL statements, or first-child threads that exceed the alert monitoring threshold.
      SQL Call Count Exceeded Problem for Non Extra Threads
      Includes all SqlCallCountExceededProblem events for non-parallel SQL statement threads, parent threads of parallel SQL statements, or first-child threads that exceed the alert monitoring threshold.
      SQL CPU Problem for Non Extra Threads
      Includes all SqlCpuProblem events for non-parallel SQL statement threads, parent threads of parallel SQL statements, or first-child threads that exceed the alert monitoring threshold.
      SQL Elapsed Time Problem for Non Extra Threads
      Includes all SqlElapsedTimeProblem events for non-parallel SQL statement threads, parent threads of parallel SQL statements, or first-child threads that exceed the alert monitoring threshold.
      Alternatively, you can create and select your own custom scope. For more information, see Adding a ParallelismCategory scope.
    3. On the Actions to Execute tab, select The repetition count of an event increments.
    4. Specify other settings for your response as appropriate and save the response. For more information, see Adding a response.