CONCURRENTWORKLOADACTIVITIES threshold

The CONCURRENTWORKLOADACTIVITIES threshold specifies the maximum number of coordinator and nested activities that can concurrently run in a workload occurrence.

Type: Aggregate
Definition domain: Workload
Enforcement scope: Workload occurrence
Tracked work: Recognized coordinator and nested activities (see Activities)
Queuing: No
Unit: Number of concurrent workload activities
Predictive or reactive: Predictive

This threshold applies to a single workload occurrence. If you have multiple occurrences of a workload running concurrently, the threshold applies separately to each workload occurrence. The tracked activities include all recognized coordinator activities and any nested activities that are generated as a result of the execution of the coordinator activity. For example, unlike the CONCURRENTDBCOORDACTIVITIES threshold, if a stored procedure is called and that stored procedure executes some SQL, both the CALL statement (which is the coordinator activity) and the SQL statements executed by the stored procedure (which are the nested activities) count towards the threshold total.

COMMIT, ROLLBACK, and ROLLBACK to SAVEPOINT statements are unaffected by this threshold.

Nested activity considerations

The nested activities that are tracked by this threshold must satisfy the following criteria:

They must be a recognized coordinator activity. Nested coordinator activities that are not recognized types as described in Work identification by type, cost, or data accessed with work classes are not counted.

Example

In this example, the CONCURRENTWORKLOADACTIVITIES threshold maximum value is set to 5. The user logic causes the following sequence of operations to occur in a workload occurrence:

Issue a load command: the current number of workload activities is 1.
- The load command internally issues some SQL. The current number of workload activities is 1. (SQL generated by a utility does not count against the CONCURRENTWORKLOADACTIVITIES threshold.)
- The load command ends. The current number of workload activities is 0.
CALL the SYSPROC.SP1 stored procedure. The current number of workload activities is 1.
- The SYSPROC.SP1 stored procedure generates some SQL. The current number of workload activities is 1. (SQL generated by a utility does not count against the CONCURRENTWORKLOADACTIVITIES threshold.)
- The SYSPROC.SP1 stored procedure ends. The current number of workload activities is 0.
Open a cursor C1. The current number of workload activities is 1.
Issue a runstats command. The current number of workload activities is 1.
- The runstats command generates some SQL. The current number of workload activities is 1.
- The runstats command ends. The current number of workload activities is 1.
Close the cursor C1. The current number of workload activities is 0.
CALL the BOB.SP1 stored procedure. The current number of workload activities is 1.
- The BOB.SP1 stored procedure opens three cursors. The current number of workload activities is 4.
- The BOB.SP1 stored procedure calls the SYSPROC.SP2 stored procedure. The current number of workload activities is 5.
  - The SYSPROC.SP2 stored procedure issues some SQL. The current number of workload activities is 5.
  - The SYSPROC.SP2 stored procedure ends. The current number of workload activities is 4.
- The BOB.SP1 stored procedure calls the BOB.SP2 stored procedure. The current number of workload activities is 5.
  - The BOB.SP2 stored procedure issues some SQL. At this point, the threshold is triggered.
  - The BOB.SP2 stored procedure ends. The current number of workload activities is 4.
- The BOB.SP1 stored procedure ends. The current number of workload activities is 0.
Open a cursor C2. The current number of workload activities is 1.
CALL the BOB.SP2 stored procedure. The current number of workload activities is 2.