DB2 10.5 for Linux, UNIX, and Windows

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.

If the work action set is defined for a database, the work actions in the work action set must be any of the following actions:

A threshold
The following thresholds apply to each individual activity in the matching work class:
- ACTIVITYTOTALTIME
- CPUTIME
- ESTIMATEDSQLCOST
- 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".

If the work actions in the work action set defined for a database are not any of these actions, SQL4720N is returned.

If you are defining a work action set for a service superclass, the work actions in the work action set must be any of the following actions:

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.

If the work actions in the work action set defined for a service superclass are not any of these actions, SQL4720N is returned.

If the work action set is defined for a workload, the work actions in the work action set must be any of the following actions:

A threshold
The following thresholds apply to each individual activity in the matching work class:
- ACTIVITYTOTALTIME
- CPUTIME
- ESTIMATEDSQLCOST
- 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.

If the work actions in the work action set defined for a workload are not any of these actions, SQL4720N is returned.

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.

Figure 1. Example of work actions, work actions sets, work classes, and work class set

The work action sets are as follows:

Database large activities contains:
- Concurrency threshold for large reads, which permits two large reads to run concurrently, and five large reads to be queued
- Rows returned threshold for large reads, which prevents large reads from returning more than 1000 rows
- Count 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 subclass 1
- Map for large writes, which prevents large writes from executing.
- Map for LOAD, which maps loads to service subclass 2

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.