DB2 10.5 for Linux, UNIX, and Windows

CONCURRENTDBCOORDACTIVITIES threshold

The CONCURRENTDBCOORDACTIVITIES threshold specifies the maximum number of recognized coordinator activities that can run concurrently in the specified definition domain and enforcement scope.

The use of this type of threshold is best suited for applications that do not execute more than one activity at a time. If an application starts more than one activity concurrently, such as issuing an UPDATE SQL statement while a cursor is open, then certain queue contention scenarios can sometimes occur depending on the concurrency level allowed by the threshold and the behaviors of the other applications involved. If this threshold exists in scenarios where applications can execute more than one activity concurrently or the application behavior is unknown, then it is recommended to have an ACTIVITYTOTALTIME threshold defined for those activities to help automatically resolve any potential queue contention scenarios.

Type: Aggregate
Definition domain: Database, work action, service superclass, service subclass
Enforcement scope: Database in environments other than DB2® pureScale® environments; Member in a DB2 pureScale environment
Tracked work: Recognized coordinator and nested activities (see Work identification by type, cost, or data accessed with work classes)
Queuing: Yes
Unit: Number of concurrent database activities
Predictive or reactive: Predictive

This threshold is a generalization of the CONCURRENTWORKLOADACTIVITIES threshold. The CONCURRENTWORKLOADACTIVITIES applies only to activities that are running in a workload domain, but you can apply the CONCURRENTDBCOORDACTIVITIES threshold to a variety of domains, ranging from the entire database to a single work action. The CONCURRENTDBCOORDACTIVITIES threshold tracks coordinator activities for non-CALL statements and any nested activities generated by CALL statements. Unlike the CONCURRENTWORKLOADACTIVITIES threshold, the CONCURRENTDBCOORDACTIVITIES threshold is a queuing threshold.

Note: If a threshold action of CONTINUE is specified for a queuing threshold, it effectively makes the size of the queue unbounded, regardless of any hard value specified for the queue size. Use the concurrent_act_top monitor element to determine the highest concurrency of activities (including nested activities) that was reached on a member for a service subclass in the time interval collected.

When creating queuing thresholds of the CONCURRENTDBCOORDACTIVITIES type, be aware of configurations that might lead to irreconcilable queue-based contention. For example:

A concurrency threshold of type CONCURRENTDBCOORDACTIVITIES is created with a maximum concurrency value of 1 and a queue size greater than 1.
An application opens a cursor that the DB2 data server recognizes as activity A1, which consumes the unique ticket that is available for the threshold.
While the cursor is still open, the application issues an UPDATE statement, which the data server recognizes as activity A2, and which is also subject to the concurrency threshold. Because the A1 activity is already running, the new activity A2 is queued.
The application is now in a queue contention state that cannot be resolved. It is waiting for A2 to execute but A2 is waiting for A1 to finish executing. This situation will not resolve itself without external intervention unless an ACTIVITYTOTALTIME threshold is also defined over these activities. If ACTIVITYTOTALTIME threshold is defined, then the situation will be resolved when A1 violates that threshold by spending more than the maximum amount of time that the data server should spend processing that activity.

This example can be generalized to multiple applications and queues. You can resolve this situation by increasing the concurrency values, or cancelling certain activities if the concurrency values are correctly set.

To reduce the chance of creating irreconcilable queue contention scenarios, the CONCURRENTDBCOORDACTIVITIES threshold affects different types of activities as follows:

CALL statements are not controlled by the threshold, but all nested child activities are under threshold control. Note that both anonymous blocks and autonomous routines are classified as CALL statements.
User defined functions (UDFs) are under threshold control, but child activities nested within UDFs are not controlled. If an autonomous routine is called from within a user defined function, neither the autonomous routine and nor any child activities of the autonomous routine are under threshold control.
Trigger actions that invoke CALL statements and the child activities of these CALL statements are not under threshold control. Note that the INSERT, UPDATE, and DELETE statements themselves that can cause a trigger to activate are under threshold control.