Work actions and the work action set domain
You can define a work action set for a database, a service superclass, or workload. The type of work actions that can be defined for a work action set depends on the type of object the work action set is defined for.
- A thresholdThe following thresholds apply to each individual activity in the matching work class:
- ACTIVITYTOTALTIME
- ACTIVITYTOTALRUNTIME
- CPUTIME
- ESTIMATEDSQLCOST
- SORTSHRHEAPUTILAttention: This feature is available in Db2 Version 11.5 Mod Pack 2 and later versions.
- SQLROWSREAD
- SQLROWSRETURNED
- SQLTEMPSPACE
The following threshold applies to all activities in the matching work class as a group:- CONCURRENTDBCOORDACTIVITIES
The actual threshold is specified by the WHEN threshold-type clause. Multiple threshold work actions can be applied to a single work class if all the thresholds are of different types. If this action is specified, the threshold is applied to all database activities associated with the work class.
- PREVENT EXECUTION
If this action is specified, all database activities that match the associated work class are not permitted to run.
- COLLECT ACTIVITY DATA
If this action is specified, information about the database activities corresponding to the work class for which this work action is defined are written to the active ACTIVITIES event monitor when the activities complete execution. For more information, see
Collecting data for individual activities
. - COUNT ACTIVITY
If this action is specified, all database activity that maps to the associated work class causes the turnstile counter for that work class type to be incremented. (The turnstile counter for the work class is incremented by 1 each time an activity is associated with that work class). The COUNT ACTIVITY work action provides an efficient way to ensure this counter is updated. If no work action is applied to an activity corresponding to a work class, the work class activity counter is not incremented. Sometimes the only action you care about is obtaining a count of activities of a given type. For more information, see
Collecting data for individual activities
.
- A mapping action
You can map an activity to any service subclass in the service superclass except for the default service subclass. You specify the service subclass to map the activity to using the MAP ACTIVITY TO SERVICE CLASS keyword. Only one map work action in the work action set can be applied to the same work class.
- PREVENT EXECUTION
Behavior is the same as for the database work action.
- COLLECT ACTIVITY DATA
Behavior is the same as for the database work action.
- COLLECT AGGREGATE ACTIVITY DATA
If this action is specified, aggregate database activity data that corresponds to the work class for which this work action is defined is collected.
- COUNT ACTIVITY
Behavior is the same as for the database work action.
- A thresholdThe following thresholds apply to each individual activity in the matching work class:
- ACTIVITYTOTALTIME
- ACTIVITYTOTALRUNTIME
- CPUTIME
- ESTIMATEDSQLCOST
- SORTSHRHEAPUTILAttention: This feature is available in Db2 Version 11.5 Mod Pack 2 and later versions.
- SQLROWSREAD
- SQLROWSRETURNED
- SQLTEMPSPACE
The following threshold applies to all activities in the matching work class as a group:- CONCURRENTDBCOORDACTIVITIES
The actual threshold is specified by the WHEN threshold-type clause. Multiple threshold work actions can be applied to a single work class if all the thresholds are of different types. If this action is specified, the threshold is applied to all database activities associated with the work class.
- PREVENT EXECUTION
Behavior is the same as for the database work action.
- COLLECT ACTIVITY DATA
Behavior is the same as for the database work action.
- COLLECT AGGREGATE ACTIVITY DATA
Behavior is the same as for the service superclass work action.
- COUNT ACTIVITY
Behavior is the same as for the database work action.
Example: Applying work class activities
The following figure shows an example of how the work classes in a work class set called LARGE ACTIVITIES are to be applied to both the database and a service superclass. To meet this objective, two work action sets, "Database large activities" and "Service class large activities" are created.
Although this example does not show it, you can also apply the classes in the LARGE ACTIVITIES work class set to a workload, by creating a work action set associated with the workload and then associating the work action set with the LARGE ACTIVITIES work class set.
Database large activities
contains:Concurrency threshold for large reads
, which permits two large reads to run concurrently, and five large reads to be queuedRows returned threshold for large reads
, which prevents large reads from returning more than 1000 rowsCount activity for load
, which counts the number of times the load utility runs on the database.
Service class large activities
contains:Map for large reads
, which maps large reads to service subclass1
Map for large writes
, which prevents large writes from executing.Map for LOAD
, which maps loads to service subclass2
A work action set does not have to contain an action for every work class in the work class set to which the work action set is applied. In addition, a work class can have more than one work action applied to it as long as the action types are different. A work class can have more than one threshold work action applied to it as long as the threshold types are different.