The ALTER WORK ACTION SET statement alters a work action
set by adding, altering, or dropping work actions within the work
action set.
Invocation
This statement can be embedded
in an application program or issued interactively. It is an executable
statement that can be dynamically prepared only if DYNAMICRULES run
behavior is in effect for the package (SQLSTATE 42509).
Authorization
The privileges
held by the authorization ID of the statement must include at least
one of the following authorities:
- SQLADM authority, only if every alteration clause
is a COLLECT clause
- WLMADM authority
- DBADM authority
Syntax
>>-ALTER WORK ACTION SET--work-action-set-name------------------>
.-----------------------------------------------------.
V (1) |
>----+-----+-ADD--| work-action-definition |---------+-+-+-----><
| +-ALTER--| work-action-alteration |-------+ |
| | .-WORK ACTION-. | |
| '-DROP--+-------------+--work-action-name-' |
| (2) |
'-------+-ENABLE--+-------------------------------'
'-DISABLE-'
work-action-definition
.-WORK ACTION-.
|--+-------------+--work-action-name---------------------------->
>--ON WORK CLASS--work-class-name------------------------------->
>--| action-types-clause |--| histogram-template-clause |------->
.-ENABLE--.
>--+---------+--------------------------------------------------|
'-DISABLE-'
action-types-clause
.-WITH NESTED----.
|--+-MAP ACTIVITY--+----------------+--TO--service-subclass-name----------+--|
| '-WITHOUT NESTED-' |
+-WHEN--| threshold-predicate-clause |--| threshold-exceeded-actions |-+
+-PREVENT EXECUTION----------------------------------------------------+
+-COUNT ACTIVITY-------------------------------------------------------+
+-COLLECT ACTIVITY DATA--| collect-activity-data-clause |--------------+
| .-BASE-----. |
'-COLLECT AGGREGATE ACTIVITY DATA--+----------+------------------------'
'-EXTENDED-'
threshold-predicate-clause
(3)
|------+-CONCURRENTDBCOORDACTIVITIES-->--integer--+----------------------------------+----------+--|
| +-AND QUEUEDACTIVITIES-->--integer-+ |
| '-AND QUEUEDACTIVITIES UNBOUNDED---' |
+-SQLTEMPSPACE-->--integer--+-K-+--------------------------------------------------------+
| +-M-+ |
| '-G-' |
+-SQLROWSRETURNED-->--integer------------------------------------------------------------+
+-ESTIMATEDSQLCOST-->--bigint------------------------------------------------------------+
+-CPUTIME-->--integer-value--+-HOUR----+--+--------------------------------------------+-+
| +-HOURS---+ '-CHECKING EVERY--integer-value--+-SECOND--+-' |
| +-MINUTE--+ '-SECONDS-' |
| '-MINUTES-' |
+-SQLROWSREAD-->--bigint-value--+--------------------------------------------+-----------+
| '-CHECKING EVERY--integer-value--+-SECOND--+-' |
| '-SECONDS-' |
'-ACTIVITYTOTALTIME-->--integer--+-DAY-----+---------------------------------------------'
+-DAYS----+
+-HOUR----+
+-HOURS---+
+-MINUTE--+
+-MINUTES-+
'-SECONDS-'
threshold-exceeded-actions
.-COLLECT ACTIVITY DATA NONE----------------------------------.
|--+-------------------------------------------------------------+-->
'-COLLECT ACTIVITY DATA----| collect-activity-data-clause |---'
>--+-STOP EXECUTION-+-------------------------------------------|
'-CONTINUE-------'
collect-activity-data-clause
.-MEMBER-.
.-ON COORDINATOR--+--------+-.
|--+----------------------------+------------------------------->
| .-MEMBERS-. |
'-ON ALL--+---------+--------'
.-WITHOUT DETAILS-----------------------------------------------------.
>--+---------------------------------------------------------------------+--|
| .-,-----------------------------------------. |
| V (4) | |
'-WITH----+-DETAILS-------------------------------+-+--+------------+-'
'-SECTION--+--------------------------+-' '-AND VALUES-'
| (5) |
'-INCLUDE ACTUALS BASE-----'
histogram-template-clause
.-ACTIVITY LIFETIME HISTOGRAM TEMPLATE--SYSDEFAULTHISTOGRAM-.
|--●--+-----------------------------------------------------------+-->
'-ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name-------'
.-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--SYSDEFAULTHISTOGRAM-.
>--●--+------------------------------------------------------------+-->
'-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name-------'
.-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--SYSDEFAULTHISTOGRAM-.
>--●--+--------------------------------------------------------------+-->
'-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name-------'
.-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--SYSDEFAULTHISTOGRAM-.
>--●--+----------------------------------------------------------------+-->
'-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name-------'
.-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--SYSDEFAULTHISTOGRAM-.
>--●--+-------------------------------------------------------------------+--●--|
'-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name-------'
work-action-alteration
.-WORK ACTION-.
|--+-------------+--work-action-name---------------------------->
.-----------------------------------------------------------------------------.
V (6) |
>----------+-SET WORK CLASS--work-class-name-----------------------------------+-+--|
+-| alter-action-types-clause |-------------------------------------+
+-●--ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name--●---------+
+-●--ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name--●--------+
+-●--ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name--●------+
+-●--ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name--●----+
+-●--ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name--●-+
'-+-ENABLE--+-------------------------------------------------------'
'-DISABLE-'
alter-action-types-clause
.-WITH NESTED----.
|--+-MAP ACTIVITY--+----------------+--TO--service-subclass-name------------------------+--|
| '-WITHOUT NESTED-' |
+-WHEN--+-| threshold-predicate-clause |--+-PERFORM ACTION-----------------------+-+-+
| | '-| alter-threshold-exceeded-actions |-' | |
| '-EXCEEDED--| alter-threshold-exceeded-actions |---------------------------' |
+-PREVENT EXECUTION------------------------------------------------------------------+
+-COUNT ACTIVITY---------------------------------------------------------------------+
+-COLLECT ACTIVITY DATA--| alter-collect-activity-data-clause |----------------------+
| .-BASE-----. |
'-COLLECT AGGREGATE ACTIVITY DATA--+----------+--------------------------------------'
'-EXTENDED-'
alter-threshold-exceeded-actions
.---------------------------------------------------------------------------------.
V (7) (8) |
|--------------+-COLLECT ACTIVITY DATA--+-| alter-collect-activity-data-clause |-+-+-+--|
| '-NONE-----------------------------------' |
'-+-STOP EXECUTION-+------------------------------------------------'
'-CONTINUE-------'
alter-collect-activity-data-clause
.-MEMBER-.
|--+-ON COORDINATOR--+--------+-+------------------------------->
| .-MEMBERS-. |
'-ON ALL--+---------+--------'
>--+-WITHOUT DETAILS-------------------------------------------------+--|
| .-,-------------------------------------. |
| V (9) | |
'-WITH----+-DETAILS---------------------------+-+--+------------+-'
'-SECTION--+----------------------+-' '-AND VALUES-'
'-INCLUDE ACTUALS BASE-'
Notes:
- The ADD, ALTER, and DROP clauses are processed in the order
in which they are specified.
- The ENABLE or DISABLE clause can only be specified once in
the same statement.
- Only one work action of the same threshold type can be applied
to a single work class at a time.
When altering a threshold work action, the threshold predicate cannot
be changed.
- The DETAILS keyword
is the minimum to be specified, followed by the option separated by
a comma.
- This
clause does not apply to thresholds.
- The same clause must not be specified more than once.
- The same clause must not be specified more than once.
- If an existing work action does not have
a threshold-exceeded action defined for it and it is being altered
to become a threshold work action, then either STOP EXECUTION or CONTINUE
must be specified, and if COLLECT ACTIVITY DATA is not specified,
then COLLECT ACTIVITY DATA NONE is the default.
- The DETAILS keyword is the minimum to
be specified, followed by the option separated by a comma.
Description
- work-action-set-name
- Identifies the work action set that is to be altered. This
is a one-part name. It is an SQL identifier (either ordinary or delimited). The work-action-set-name must
identify a work action set that exists at the current server (SQLSTATE
42704).
- ADD
- Adds a work action to the work action set.
- WORK ACTION work-action-name
- Names the work action. The work-action-name must
not identify a work action that already exists at the current server
under this work action set (SQLSTATE 42710). The work-action-name cannot
begin with 'SYS' (SQLSTATE 42939).
- ON WORK CLASS work-class-name
- Specifies the work class that identifies the database activities
to which this work action will apply. The work-class-name must
exist in the work-class-set-name at the current
server (SQLSTATE 42704).
- MAP ACTIVITY
- Specifies a work action of mapping the activity. This action can
only be specified if the object for which this work action set is
defined is a service superclass (SQLSTATE 5U034).
- WITH NESTED or WITHOUT NESTED
- Specifies whether or not activities that are nested under this
activity are mapped to the service subclass. The default is WITH NESTED.
- WITH NESTED
- All database activities that have a nesting level of zero that
are classified under the work class, and all database activities nested
under this activity, are mapped to the service subclass; that is,
activities with a nesting level greater than zero are run under the
same service class as activities with a nesting level of zero.
- WITHOUT NESTED
- Only database activities that have a nesting level of zero that
are classified under the work class are mapped to the service subclass.
Database activities that are nested under this activity are handled
according to their activity type.
- TO service-subclass-name
- Specifies the service subclass to which activities are to be mapped.
The service-subclass-name must already exist in
the service-superclass-name at the current server
(SQLSTATE 42704). The service-subclass-name cannot
be the default service subclass, SYSDEFAULTSUBCLASS (SQLSTATE 5U018).
- WHEN
- Specifies the threshold that will be applied to the database activity
that is associated with the work class for which this work action
is defined. A threshold can only be specified if the database manager
object for which this work action set is defined is a database (SQLSTATE
5U034). None of these thresholds apply to internal database activities
initiated by the database manager or to database activities generated
by administrative SQL routines.
- threshold-predicate-clause
- For a description of valid threshold types, see the "CREATE
THRESHOLD" statement.
- threshold-exceeded-actions
- For a description of valid threshold-exceeded actions, see the "CREATE
THRESHOLD" statement.
- PREVENT EXECUTION
- Specifies that none of the database activities associated with
the work class for which this work action is defined will be allowed
to run (SQLSTATE 5U033).
- COUNT ACTIVITY
- Specifies that all of the database activities associated with
the work class are to be run and that each time one is run, the counter
for the work class will be incremented.
- COLLECT ACTIVITY DATA
- Specifies that data about each activity associated with the work
class for which this work action is defined is to be sent to any active
activities event monitor when the activity completes.
- collect-activity-data-clause
- ON COORDINATOR MEMBER
- Specifies that the activity data is to be collected
at only the coordinator member of
the activity.
- ON ALL MEMBERS
- Specifies that the activity data is to be collected
at all members on
which the activity is processed. For predictive thresholds, activity
information is collected at all members only
if you also specify the CONTINUE action for exceeded
thresholds. For reactive thresholds, the ON ALL MEMBERS clause
has no effect and activity information is always collected only at
the coordinator member.
For both predictive and reactive thresholds, any input data values, section information,
or values will be collected only at the coordinator member.
- WITHOUT DETAILS
- Specifies that data about each activity associated
with the work class for which this work action is defined should be
sent to any active activities event monitor, when the activity completes
execution. Details about statement, compilation environment, and section
environment data are not sent.
- WITH
- DETAILS
- Specifies that statement and compilation environment data is to
be sent to any active activities event monitor, for those activities
that have them. Section environment data is not sent.
- SECTION
- Specifies that statement, compilation environment and section
environment data is to be sent to any active activities event monitor
for those activities that have them. DETAILS must
be specified if SECTION is specified.
- INCLUDE ACTUALS BASE
- Specifies that section actuals should also be collected on any
partition where the activity data is collected. For section actuals
to be collected, either INCLUDE ACTUALS clause must be specified or
the section_actuals database configuration parameter
must be set.
The effective setting for the collection of section
actuals is the combination of the INCLUDE ACTUALS clause, the section_actuals database
configuration parameter, and the <collectsectionactuals> setting
specified on the WLM_SET_CONN_ENV routine. For example, if INCLUDE
ACTUALS BASE is specified, yet the section_actuals database
configuration parameter value is NONE and <collectsectionactuals>
is set to NONE, then the effective setting for the collection of section
actuals is BASE.
BASE specifies that the following actuals should
be enabled and collected during the activity's execution:
- Basic operator cardinality counts
- Statistics for each object referenced (DML statements only)
- AND VALUES
- Specifies that input data values are to be sent
to any active activities event monitor, for those activities that
have them.
- NONE
- Specifies that activity data should not be collected for each
activity that is associated with the work class for which this work
action is defined.
- COLLECT AGGREGATE ACTIVITY DATA
- Specifies that aggregate activity data is to be captured for activities
that are associated with the work class for which this work action
is defined and sent to the
statistics event monitor, if one is active. This information
is collected periodically on an interval that is specified by the wlm_collect_int database
configuration parameter. The default is COLLECT AGGREGATE ACTIVITY
DATA BASE. This clause cannot be specified for a work action defined
in a work action set that is applied to a database.
- BASE
- Specifies that basic aggregate activity data should be captured
for activities associated with the work class for which this work
action is defined and sent to the
statistics event monitor, if one is active. Basic aggregate
activity data includes:
- Estimated activity cost high watermark
- Rows returned high watermark
- Temporary
table space usage high watermark. Only activities that have an SQLTEMPSPACE
threshold applied to them participate in this high watermark.
- Activity life time histogram
- Activity queue time histogram
- Activity execution time histogram
- EXTENDED
- Specifies that all aggregate activity data should be captured
for activities associated with the work class for which this work
action is defined and sent to the
statistics event monitor, if one is active. This includes
all basic aggregate activity data plus:
- Activity data manipulation language (DML) estimated cost histogram
- Activity DML inter-arrival time histogram
- ENABLE or DISABLE
- Specifies whether or not the work action is to be considered when
database activities are submitted. The default is ENABLE.
- ENABLE
- Specifies that the work action is enabled and will be considered when
database activities are submitted.
- DISABLE
- Specifies that the work action is disabled and will not be considered when
database activities are submitted.
- histogram-template-clause
- Specifies histogram templates to use when collecting aggregate
activity data for activities associated with the work class to which
this work action is assigned. Aggregate activity data is only collected
for the work class when the work action type is COLLECT AGGREGATE
ACTIVITY DATA.
- ACTIVITY LIFETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the duration, in milliseconds, of database activities running
during a specific interval. The database activities are those associated
with the work class to which this work action is assigned. This time
includes both time queued and time executing. The default is SYSDEFAULTHISTOGRAM.
This information is only collected when the COLLECT AGGREGATE ACTIVITY
DATA clause is specified, with either the BASE or EXTENDED option.
- ACTIVITY QUEUETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the length of time, in milliseconds, that database activities
are queued during a specific interval. The database activities are
those associated with the work class to which this work action is
assigned. The default is SYSDEFAULTHISTOGRAM. This information is
only collected when the COLLECT AGGREGATE ACTIVITY DATA clause is
specified, with either the BASE or EXTENDED option.
- ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the length of time, in milliseconds, that database activities
are executing during a specific interval. The database activities
are those associated with the work class to which this work action
is assigned. This time does not include the time spent queued. Activity
execution time is collected in this histogram at each member where
the activity executes. On the activity's coordinator member,
this is the end-to-end execution time (that is, the life time less
the time spent queued). On non-coordinator members,
this is the time that these members spend
working on behalf of the activity. During the execution of a given
activity, the database manager might present work to a non-coordinator
member more
than once, and each time the non-coordinator member will
collect the execution time for that occurrence of the activity. Therefore,
the counts in the execution time histogram might not represent the
actual number of unique activities that executed on a member.
The default is SYSDEFAULTHISTOGRAM. This information is only collected
when the COLLECT AGGREGATE ACTIVITY DATA clause is specified, with
either the BASE or EXTENDED option.
- ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE template-name
- Specifies the template that describes the histogram used to collect
statistical data about the estimated cost, in timerons, of DML activities
associated with the work class to which this work action is assigned.
The default is SYSDEFAULTHISTOGRAM. This information is only collected
when the COLLECT AGGREGATE ACTIVITY DATA clause is specified with
the EXTENDED option.
- ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE template-name
- Specifies the template that describes the histogram used to collect
statistical data about the length of time, in milliseconds, between
the arrival of one DML activity and the arrival of the next DML activity,
for any activity associated with the work class to which this work
action is assigned. The default is SYSDEFAULTHISTOGRAM. This information
is only collected when the COLLECT AGGREGATE ACTIVITY DATA clause
is specified with the EXTENDED option.
- ALTER
- Alters the definition of the work action. You can change the work
class to which this work action applies, and the action that is to
be applied to the database activity that falls within the work class.
- WORK ACTION work-action-name
- Identifies the work action. The work-action-name must
identify a work action that exists at the current server under this
work action set (SQLSTATE 42704).
- SET WORK CLASS work-class-name
- Specifies the work class that identifies the database activities
to which this work action will apply. The work-class-name must
exist in the work-class-set-name at the current
server (SQLSTATE 42704).
- MAP ACTIVITY
- Specifies a work action of mapping the activity. This action can
only be specified if the object for which this work action set is
defined is a service superclass (SQLSTATE 5U034).
- WITH NESTED or WITHOUT NESTED
- Specifies whether or not activities that are nested under this
activity are mapped to the service subclass. The default is WITH NESTED.
- WITH NESTED
- All database activities that have a nesting level of zero that
are classified under the work class, and all database activities nested
under this activity are mapped to the service subclass.
- WITHOUT NESTED
- Only database activities that have a nesting level of zero that
are classified under the work class are mapped to the service subclass.
Database activities that are nested under this activity are handled
according to their activity type.
- TO service-subclass-name
- Specifies the service subclass to which activities are to be mapped.
The service-subclass-name must already exist in
the service-superclass-name at the current server
(SQLSTATE 42704). The service-subclass-name cannot
be the default service subclass, SYSDEFAULTSUBCLASS (SQLSTATE 5U018).
- WHEN
- Specifies the threshold to be altered for the database activity
that is associated with the work class for which this work action
is defined.
- threshold-predicate-clause
- For a description of valid threshold types, see the "CREATE
THRESHOLD" statement.
- PERFORM ACTION
- When altering the value of the threshold predicate
condition, specifies that the threshold exceeded action is not changed.
The work action must be a threshold (SQLSTATE 42613).
- alter-threshold-exceeded-actions
- For a description of valid alter-threshold-exceeded-actions,
see threshold-exceeded-actions in the "CREATE THRESHOLD" statement.
- EXCEEDED
- Specifies to keep the same threshold predicate
that was specified originally for this altered threshold. The work
action must be a threshold (SQLSTATE 42613).
- PREVENT EXECUTION
- Specifies that none of the database activities associated with
the work class for which this work action is defined will be allowed
to run (SQLSTATE 5U033).
- COUNT ACTIVITY
- Specifies that all of the database activities associated with
the work class are to be run and that each time one is run, the counter
for the work class will be incremented.
- COLLECT ACTIVITY DATA
- Specifies that data about each activity associated with the work
class for which this work action is defined is to be sent to any active
activities event monitor when the activity completes.
- alter-collect-activity-data-clause
- ON COORDINATOR MEMBER
- Specifies that the activity data is to be collected only at the
coordinator member of the activity.
- ON ALL MEMBERS
- Specifies that activity
data is to be collected at all members where
the activity is processed. On remote members,
a record for the activity may be captured multiple times as the activity
comes and goes on those members.
If the AND VALUES clause is specified, activity input values will
be collected only for the members of
the coordinator.
- WITHOUT DETAILS
- Specifies that data about each activity that
is associated with the work class for which this work action is defined
should be sent to any active activities event monitor when the activity
completes execution. Details about statement, compilation environment,
and section environment data are not sent.
- WITH
- DETAILS
- Specifies that statement and compilation environment data is to
be sent to any active activities event monitor, for those activities
that have them. Section environment data is not sent.
- SECTION
- Specifies that statement, compilation environment, section environment
data, and section actuals are to be sent to any active activities
event monitor for those activities that have them. DETAILS must
be specified if SECTION is specified. Section actuals
will be collected on any member where the activity data is collected.
- INCLUDE ACTUALS BASE
- Specifies that section actuals should also be collected on any
partition where the activity data is collected. For section actuals
to be collected, either INCLUDE ACTUALS clause must be specified or
the section_actuals database configuration parameter
must be set.
The effective setting for the collection of section
actuals is the combination of the INCLUDE ACTUALS clause, the section_actuals database
configuration parameter, and the <collectsectionactuals> setting
specified on the WLM_SET_CONN_ENV routine. For example, if INCLUDE
ACTUALS BASE is specified, yet the section_actuals database
configuration parameter value is NONE and <collectsectionactuals>
is set to NONE, then the effective setting for the collection of section
actuals is BASE.
BASE specifies that the following actuals should
be enabled and collected during the activity's execution:
- Basic operator cardinality counts
- Statistics for each object referenced (DML statements only)
- AND VALUES
- Specifies that input data values are to be sent
to any active activities event monitor, for those activities that
have them.
- NONE
- Specifies that activity data should not be collected for each
activity that is associated with the work class for which this work
action is defined.
- COLLECT AGGREGATE ACTIVITY DATA
- Specifies that aggregate activity data is to be captured for activities
that are associated with the work class for which this work action
is defined and sent to the
statistics event monitor, if one is active. This information
is collected periodically on an interval that is specified by the wlm_collect_int database
configuration parameter. The default is COLLECT AGGREGATE ACTIVITY
DATA BASE. This clause cannot be specified for a work action
defined in a work action set that is applied to a database.
- BASE
- Specifies that basic aggregate activity data should be captured
for activities associated with the work class for which this work
action is defined and sent to the
statistics event monitor, if one is active. Basic aggregate
activity data includes:
- Estimated activity cost high watermark
- Rows returned high watermark
- Temporary table space usage high watermark
- Activity life time histogram
- Activity queue time histogram
- Activity execution time histogram
- EXTENDED
- Specifies that all aggregate activity data should be captured
for activities associated with the work class for which this work
action is defined and sent to the
statistics event monitor, if one is active. This includes
all basic aggregate activity data plus:
- Activity DML estimated cost histogram
- Activity DML inter-arrival time histogram
- ACTIVITY LIFETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the duration, in milliseconds, of database activities running
during a specific interval. This time includes both time queued and
time executing. The database activities are those associated with
the work class to which this work action is assigned. The default
is SYSDEFAULTHISTOGRAM. This information is only collected when the
COLLECT AGGREGATE ACTIVITY DATA clause is specified, with either the
BASE or EXTENDED option.
- ACTIVITY QUEUETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the length of time, in milliseconds, that database activities
are queued during a specific interval. The database activities are
those associated with the work class to which this work action is
assigned. The default is SYSDEFAULTHISTOGRAM. This information is
only collected when the COLLECT AGGREGATE ACTIVITY DATA clause is
specified, with either the BASE or EXTENDED option.
- ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE template-name
- Specifies
the template that describes the histogram used to collect statistical
data about the length of time, in milliseconds, that database activities
are executing during a specific interval. The database activities
are those associated with the work class to which this work action
is assigned. This time does not include the time spent queued. Activity
execution time is collected in this histogram at each member where
the activity executes. On the activity's coordinator member,
this is the end-to-end execution time (that is, the life time less
the time spent queued). On non-coordinator members,
this is the time that these members spend
working on behalf of the activity. During the execution of a given
activity, the database manager might present work to a non-coordinator
member more
than once, and each time the non-coordinator member will
collect the execution time for that occurrence of the activity. Therefore,
the counts in the execution time histogram might not represent the
actual number of unique activities that executed on a member.
The default is SYSDEFAULTHISTOGRAM. This information is only collected
when the COLLECT AGGREGATE ACTIVITY DATA clause is specified, with
either the BASE or EXTENDED option.
- ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE template-name
- Specifies the template that describes the histogram used to collect
statistical data about the estimated cost, in timerons, of data manipulation
language (DML) activities associated with the work class to which
this work action is assigned. The default is SYSDEFAULTHISTOGRAM.
This information is only collected when the COLLECT AGGREGATE ACTIVITY
DATA clause is specified with the EXTENDED option.
- ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE template-name
- Specifies the template that describes the histogram
used to collect statistical data about the length of time, in milliseconds,
between the arrival of one DML activity and the arrival of the next
DML activity, for any activity associated with the work class to which
this work action is assigned. The default is SYSDEFAULTHISTOGRAM.
This information is only collected when the COLLECT AGGREGATE ACTIVITY
DATA clause is specified with the EXTENDED option.
- ENABLE or DISABLE
- Specifies whether or not the work action is to be considered when
database activities are submitted.
- ENABLE
- Specifies that the work action is enabled and will be considered when
database activities are submitted.
- DISABLE
- Specifies that the work action is disabled and will not be considered when
database activities are submitted.
- DROP work-action-name
- Drops the work action from the work action set. The work-action-name must
identify a work action that exists at the current server under this
work action set (SQLSTATE 42704).
A
threshold created as part of a work action set cannot be manipulated
directly. You must first disable the work action in order to disable
the threshold. You can then drop the work action once the threshold
is not being used. For more information about
dropping a work action, see the Related tasks section.
- ENABLE or DISABLE
- Specifies whether or not the work action set is to be considered
when database activities are submitted.
- ENABLE
- Specifies that the work action set is enabled and will be considered
when database activities are submitted.
- DISABLE
- Specifies that the work action set is disabled and will not be
considered when database activities are submitted.
Rules
- A workload management (WLM)-exclusive SQL statement must be followed
by a COMMIT or a ROLLBACK statement (SQLSTATE 5U021). WLM-exclusive
SQL statements are:
- CREATE HISTOGRAM TEMPLATE, ALTER HISTOGRAM TEMPLATE, or DROP (histogram
template)
- CREATE SERVICE CLASS, ALTER SERVICE CLASS, or DROP (service class)
- CREATE THRESHOLD, ALTER THRESHOLD, or DROP (threshold)
- CREATE WORK ACTION SET, ALTER WORK ACTION SET, or DROP (work action
set)
- CREATE WORK CLASS SET, ALTER WORK CLASS SET, or DROP (work class
set)
- CREATE WORKLOAD, ALTER WORKLOAD, or DROP (workload)
- GRANT (workload privileges) or REVOKE (workload privileges)
- A WLM-exclusive SQL statement cannot be issued within a global
transaction (SQLSTATE 51041) such as, for example, an XA transaction.
Notes
- Changes are written to the system catalog, but do not take effect
until they are committed, even for the connection that issues
the statement.
- Thresholds with a queue, for example CONCURRENTDBCOORDACTIVITIES,
must be disabled before they can be dropped.
- Only one uncommitted
WLM-exclusive SQL statement at a time is allowed across all partitions.
If an uncommitted WLM-exclusive SQL statement is executing, subsequent
WLM-exclusive SQL statements will wait until the current WLM-exclusive
SQL statement commits or rolls back.
- Syntax alternatives: The following syntax alternatives are
supported for compatibility with previous versions of DB2® and
with other database products. These alternatives are non-standard and should not be used.
- DATABASE PARTITION can be specified in place of MEMBER, except when the
DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- DATABASE PARTITIONS can be specified in place of MEMBERS, except when the
DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
Examples
- Example 1: Alter the DATABASE_ACTIONS work action set
and add two work actions using the work class LARGE_SELECTS. For the
work action ONE_CONCURRENT_SELECT, apply a concurrency threshold of
1 to control the number of activities that can run at one time, and
allow a maximum of 3 to be queued. For work action BIG_ROWS_RETURNED,
limit the number of rows that can be returned by database activities
that fall within that class to 1 000 000.
ALTER WORK ACTION SET DATABASE_ACTIONS
ADD WORK ACTION ONE_CONCURRENT_SELECT ON WORK CLASS LARGE_SELECTS
WHEN CONCURRENTDBCOORDACTIVITIES > 1
AND QUEUEDACTIVITIES > 3 STOP EXECUTION
ADD WORK ACTION BIG_ROWS_RETURNED ON WORK CLASS LARGE_SELECTS
WHEN SQLROWSRETURNED > 1000000 STOP EXECUTION
- Example 2: Alter the ADMIN_APPS_ACTIONS work action
set to alter the MAP_SELECTS work action to map all activities that
run in super service class ADMIN_APPS under the work class SELECT_CLASS
to the service subclass ALL_SELECTS. Also add a new work action called
MAP_UPDATES that maps all activities that would run in the work class
UPDATE_CLASS to the service subclass ALL_SELECTS.
ALTER WORK ACTION SET ADMIN_APPS_ACTIONS
ALTER WORK ACTION MAP_SELECTS MAP ACTIVITY TO ALL_SELECTS
ADD WORK ACTION MAP_UPDATES ON WORK CLASS UPDATE_CLASS
MAP ACTIVITY TO ALL_SELECTS