Creating a work action

Use the CREATE WORK ACTION SET statement or the ALTER WORK ACTION SET statement to create a work action.

Before you begin

To create a work action, you require WLMADM or DBADM authority.

For additional prerequisites, see the following topics:

When you create a work action:

You associate a work action with a work class. The work class must already exist in the work class set that the work action set is applied to.
If the work action is a threshold, the work action set must be defined for a workload or for the database. For the list of supported thresholds for work actions, see Thresholds that can be used in work actions.
If you are creating a mapping work action, the work action set must be defined for a service superclass. The service subclass being mapped to must already exist in the service superclass this work action set is being defined for. In addition, you cannot specify the default service subclass.
More than one work action can be applied to a single work class, but they must be different work action types. For example, you can apply a mapping work action and a collect work action to the same work class. You can, however, only apply one work action of the same type to the same work class. For example, you cannot apply more than one mapping work action to the same work class. The one exception to this is if the work action represents a threshold. You can apply more than one threshold work action to a single work class but each threshold must be of a different type.
If you are creating a collect aggregate activity data work action, the work action set must be defined for a service superclass or a workload.

Procedure

To create a work action:

Use the work-action-definition keyword of the CREATE WORK ACTION SET statement, or the ADD work-action-definition keyword of the ALTER WORK ACTION SET statement.
Specify one or more of the following for the work action:
- A name for the work action. The name of the work action must be unique within the work action set.
- The name of the work class to which this work action applies. The work class must be one of the work classes in the work class set that the work action set is associated with. For example, to apply this work action to the work class LARGEDML, you would specify:
```
ON WORK CLASS LARGEDML
```
- The action that is to apply to activities that match the work class for this work action:
  - If the work action set is associated with a service superclass, you can specify the MAP ACTIVITY keyword so that the work action maps activities to a service subclass in the service superclass. By default, mapping work actions cause activities that are nested to be mapped to the same service subclass as its parent. A cursor that has been opened inside a routine is an example of a nested activity.
    For example, if you want the work action to map to the service subclass SMALLREAD, and you want all nested activities to be mapped to the same service subclass, you would specify:
```
MAP ACTIVITY TO SMALLREAD
```
    You could also specify:
```
MAP ACTIVITY WITH NESTED TO SMALLREAD
```
    If you want the work action to map to the service subclass and to not map nested activities to this service subclass, you would specify:
```
MAP ACTIVITY WITHOUT NESTED TO SMALLREAD
```
    If you define the work action as WITHOUT NESTED, nested activities are handled according to their activity type instead of automatically being mapped to the same service subclass as the parent activity. For example, if a CALL activity is mapped to service subclass subsc1, and the routine has an open cursor inside it, the open cursor might be mapped to a different service subclass if it falls under another work class that has another mapping work action applied to it.
- If the work action set is associated with a database or a workload, you can specify a WHEN keyword to indicate a threshold to apply to the activity, and the action to take if the activity causes the threshold to be violated. You can specify the following thresholds for a work action:
  - ACTIVITYTOTALTIME
  - ESTIMATEDSQLCOST
  - CONCURRENTDBCOORDACTIVITIES
  - CPUTIME
  - SORTSHRHEAPUTIL
    Attention: This feature is available in Db2 Version 11.5 Mod Pack 2 and later versions.
  - SQLROWSREAD
  - SQLTEMPSPACE
  - SQLROWSRETURNED
  If the threshold is violated, you can specify the following actions to be taken:
  - Whether activity data is to be collected about the activity that caused the threshold to be violated. If collected, when the activity completes execution, the activity data is written to an active activities event monitor. By default, no data about the activity is collected. If you want to collect data about this activity, you can collect it from the coordinator member, a specific member, or from all database members. You have the option of collecting this data with or without details about the statement and its compilation environment. If you want to collect details about the statement and compilation environment, you can also specify that the input data values used in the activity.
  - Whether the activity that caused the threshold to be violated is to be permitted to continue running or not. By default, the activity is stopped.
  For example, if you want the work action to check for DML statements that have a cost over 2 000 timerons, collect the basic data about this activity when the threshold is violated and continue to run, you would specify:
```
WHEN ESTIMATEDSQLCOST > 2000 COLLECT ACTIVITY DATA CONTINUE
```
- To prevent any activities that correspond to the work class defined for this work action from executing, you can use the PREVENT EXECUTION keyword.
- To count the number of database activities associated with the work class without incurring the additional overhead of another action (such as collecting data or mapping an activity), you can specify the COUNT ACTIVITY keyword.
- To collect activity data for activities that fall under the work class, specify the COLLECT ACTIVITY DATA keyword. If collected, when the activity completes execution, the activity data is written to an active activities event monitor. By default, no data about the activity is collected. If you want to collect data about this activity, you can collect it from the coordinator member or from all members. If you want to collect activity details such as the statement and the compilation environment information, you can do so by specifying the WITH DETAILS keyword. You can also use the AND VALUES keyword to have input data values (for those activities that have them) sent to the activities event monitor.
  For example, assume that you have a work action set that is applied to a service superclass. You want to have activity data for all activities that are assigned to this work action written to the applicable event monitor, including all aggregate activity information, information about the compilation environment, and any input data values. You would specify:
```
COLLECT ACTIVITY DATA ON ALL WITH DETAILS AND VALUES
```
- If the work action set is associated with a service superclass or a workload, you can specify the COLLECT AGGREGATE ACTIVITY DATA keyword to collect aggregate activity data for activities that fall under the work class. If collected, aggregate activity data is captured and sent to the applicable event monitor. This information is collected periodically on an interval that is specified by the wlm_collect_int database configuration parameter.
  For example, assume that you have a work action set that is applied to a service superclass. You want to have aggregate activity data for all activities that are assigned to this work action written to the applicable event monitor, including the base data, the activity data manipulation language (DML) estimated cost histogram, and the activity DML inter-arrival time histogram. You would specify
```
COLLECT AGGREGATE ACTIVITY DATA EXTENDED
```
- The histogram templates used by a COLLECT AGGREGATE ACTIVITY DATA work action to describe the histograms created for the corresponding work class. Specifying the histogram templates used by a work action adds the corresponding rows in the SYSCAT.HISTOGRAMTEMPLATEUSE, view which displays the histogram templates referenced by the service class or work action. For example, if you want to collect interarrival statistics for the default interarrival histogram template, you would specify:
```
INTERARRIVALTIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM 
```
  For more information about histograms and histogram templates, see Histograms in workload management.
- Whether the work action is enabled or disabled. By default a work action is created as enabled, but you can specify whether it is enabled or disabled by using the ENABLE or DISABLE keyword. If the work action is disabled, the data server does not consider this work action when activities enter the database or service superclass (depending on the object you created the work action set for).
Commit your changes.
When you commit your changes, the work action is added to the SYSCAT.WORKACTIONS view. If the work action is a threshold, the threshold is added to the SYSCAT.THRESHOLDS view.
A new work action takes effect in the database only after it is committed, and does not affect any database activities currently running.