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
- 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
- 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.
- 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.