If you need to alter a work action, use the ALTER WORK
ACTION SET statement.
Before you begin
To alter a work action, you require SQLADM, WLMADM or
DBADM authority. To specify any clause other than a COLLECT clause,
the authorization ID must include WLMADM or DBADM authority.
See Workload management DDL statements for additional prerequisites.
Procedure
To alter a work action:
- Use the ALTER keyword of the ALTER WORK ACTION SET statement
to change one or more of the following characteristics of the work
action.
- You can alter the work class to which the work action is applied.
The work class must already exist in the work class set to which the
work action set is applied.
- If the work action maps to a service subclass, you can alter
which service subclass the database activity is to be mapped. You
can only change the mapping to a service subclass in the same service
superclass. You cannot map to the default service subclass. You can
also change whether nested activities in the activity are mapped to
the same service subclass or not. For example, if the work action
is currently defined as WITH NESTED, you can change this to WITHOUT
NESTED. This change would cause the nested activities to be 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 statement 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.
- You can alter the action type specified for the work action
(that is, mapping, threshold, prevent execution, count activity, collect
actions), but you must alter it to a valid work type. For example,
if the work action is to map the activity to a service subclass, you
cannot change the work action to a threshold, or the reverse. The
reason is because, in this example, the work action set must have been applied
to a service superclass in order to have a mapping action and threshold
actions are not valid for work action sets applied to service superclasses. If
you alter the type of a work action that is a threshold work action
or alter the type of work action to a threshold, the following occurs:
- If the work action was a threshold and has been changed to a non-threshold,
the threshold is removed from the SYSCAT.THRESHOLDS view.
- If the work action was not a threshold and has been changed to
a threshold, a new threshold will be created in the SYSCAT.THRESHOLDS
view.
Note: If the action is a threshold, you cannot alter the type
of threshold to a different threshold. So, for example, if the work
action was an SQLROWSRETURNED threshold, you cannot change it to a SQLTEMPSPACE
threshold. In addition, you cannot change the work action type of
an enabled CONCURRENTDBCOORDACTIVITIES work action threshold.
- You can alter the histogram templates used by a COLLECT AGGREGATE
ACTIVITY DATA work action to describe the histograms created for the
corresponding work class. Updating the histogram templates used by
a work action updates the corresponding rows in the SYSCAT.HISTOGRAMTEMPLATEUSE
view, which displays the histogram templates referenced by the service
class or work action. For more information about histograms and histogram templates, see Histograms in workload management.
- You can alter whether you want to enable or disable the work
action. By default, work actions are enabled. When enabled, the data
server considers the work action for application against the activity
that falls under the work class for the work action. If the work action
is disabled, the data server ignores it.
- Commit your changes. When you commit your
changes, the work action is updated in the SYSCAT.WORKACTIONS view.