DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE WORK ACTION SET statement

The CREATE WORK ACTION SET statement defines a work action set and 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 WLMADM or DBADM authority.

Syntax


>>-CREATE WORK ACTION SET--work-action-set-name----------------->

>--FOR--+-DATABASE-------------------------------+-------------->
        +-SERVICE CLASS--service-superclass-name-+   
        '-WORKLOAD--workload-name----------------'   

>--USING WORK CLASS SET--work-class-set-name-------------------->

                                             .-ENABLE--.   
>--+--------------------------------------+--+---------+-------><
   |    .-,--------------------------.    |  '-DISABLE-'   
   |    V                            |    |                
   '-(----| work-action-definition |-+--)-'                

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-types-clause |--| threshold-exceeded-actions |-+   
   +-PREVENT EXECUTION------------------------------------------------+   
   +-COUNT ACTIVITY---------------------------------------------------+   
   +-COLLECT ACTIVITY DATA--| collect-activity-data-clause |----------+   
   |                                  .-BASE-----.                    |   
   '-COLLECT AGGREGATE ACTIVITY DATA--+----------+--------------------'   
                                      '-EXTENDED-'                        

threshold-types-clause

    (1)                                           .-AND QUEUEDACTIVITIES > 0---------.              
|------+-CONCURRENTDBCOORDACTIVITIES-->--integer--+----------------------------------+----------+--|
       |                                          +-AND QUEUEDACTIVITIES-->--integer-+          |   
       |                                          '-AND QUEUEDACTIVITIES UNBOUNDED---'          |   
       +-SQLTEMPSPACE-->--integer--+-K-+--------------------------------------------------------+   
       |                           +-M-+                                                        |   
       |                           '-G-'                                                        |   
       +-SQLROWSRETURNED-->--integer------------------------------------------------------------+   
       +-ESTIMATEDSQLCOST-->--bigint------------------------------------------------------------+   
       |                                         .-CHECKING EVERY 60 SECONDS------------------. |   
       +-CPUTIME-->--integer-value--+-HOUR----+--+--------------------------------------------+-+   
       |                            +-HOURS---+  '-CHECKING EVERY--integer-value--+-SECOND--+-' |   
       |                            +-MINUTE--+                                   '-SECONDS-'   |   
       |                            '-MINUTES-'                                                 |   
       |                               .-CHECKING EVERY 60 SECONDS------------------.           |   
       +-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

                     .-DATABASE PARTITION-.     
   .-ON COORDINATOR--+--------------------+-.   
|--+----------------------------------------+------------------->
   |         .-DATABASE PARTITIONS-.        |   
   '-ON ALL--+---------------------+--------'   

   .-WITHOUT DETAILS---------------------------.   
>--+-------------------------------------------+----------------|
   |       .-,---------------.                 |   
   |       V           (2)   |                 |   
   '-WITH----+-DETAILS-----+-+--+------------+-'   
             '-SECTION-----'    '-AND VALUES-'     

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-------'

Notes:

Only one work action of the same threshold type can be applied to a single work class at a time.
The DETAILS keyword is the minimum to be specified, followed by the option separated by a comma.

Description

work-action-set-name

Names the work action set. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The work-action-set-name must not identify a work action set that already exists at the current server (SQLSTATE 42710). The name must not begin with the characters 'SYS' (SQLSTATE 42939).

FOR

Specifies the database manager object to which the actions in this work action set will apply. Each database manager object can have only one work action set defined for it (SQLSTATE 5U017).

DATABASE

The actions in this work action set are to apply to the database. If DATABASE is specified, the MAP ACTIVITY action cannot be specified (SQLSTATE 5U034).

SERVICE CLASS service-superclass-name

The actions in this work action set are to apply to service-superclass-name. If SERVICE CLASS is specified, threshold actions cannot be specified (SQLSTATE 5U034). The service-superclass-name must exist at the current server (SQLSTATE 42704). The service-superclass-name must not be a service subclass and cannot be any of the following classes (SQLSTATE 5U032):

The system service class (SYSDEFAULTSYSTEMCLASS)
The maintenance service class (SYSDEFAULTMAINTENANCECLASS)
The default user service class (SYSDEFAULTUSERCLASS)

WORKLOAD workload-name

The actions in this work action set are to apply to workload workload-name. If WORKLOAD is specified, the MAP ACTIVITY action cannot be specified (SQLSTATE 5U034). The workload-name must exist at the current server (SQLSTATE 42704). The workload-name cannot be the SYSDEFAULTADMWORKLOAD (SQLSTATE 5U032).

USING WORK CLASS SET work-class-set-name

Specifies the work class set containing the work classes that will classify database activities on which to perform actions. The work-class-set-name must exist at the current server (SQLSTATE 42704).

work-action-definition

Specifies the definition of the work action.

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 or a workload (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-types-clause

For a description of valid threshold types, see "CREATE THRESHOLD" statement.

threshold-exceeded-actions

For a description of valid threshold-exceeded actions, see "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 for which this work action is defined 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. The default is COLLECT ACTIVITY DATA WITHOUT DETAILS.

collect-activity-data-clause

ON COORDINATOR DATABASE PARTITION

Specifies that the activity data is to be collected at only the database partition of the coordinator of the activity.

ON ALL DATABASE PARTITIONS

Specifies that activity data is to be collected at all database partitions where the activity is processed. On remote database partitions, a record for the activity may be captured multiple times as the activity comes and goes on those partitions. If the AND VALUES clause is specified, activity input values will be collected only for the database partition of the coordinator.

WITHOUT DETAILS

Specifies that data about each activity that executes in the service class 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. If section actuals are enabled, they will be collected on any partition where the activity data is collected.
AND VALUES: Specifies that input data values are to be sent to any active activities event monitor, for those activities that have them. This data does not include SQL statements that are compiled by using the REOPT ALWAYS bind option.

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
Note: 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.

ENABLE or DISABLE

Specifies whether or not the work action set is to be considered when database activities are submitted. The default is ENABLE.

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.

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 DB2® activities-associated with the work class to which this work action is assigned-running during a specific interval. 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 DB2 activities-associated with the work class to which this work action is assigned-are queued during a specific interval. 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 DB2 activities-associated with the work class to which this work action is assigned-are executing during a specific interval. This time does not include the time spent queued. Activity execution time is collected in this histogram at each database partition where the activity executes. On the activity's coordinator database partition, this is the end-to-end execution time (that is, the life time less the time spent queued). On non-coordinator database partitions, this is the time that these partitions spend working on behalf of the activity. During the execution of a given activity, DB2 might present work to a remote database partition more than once, and each time the remote partition 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 database partition. 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.

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.
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.

Examples

Example 1: Create a work action set named DATABASE_ACTIONS to apply to all database activities. Use the LARGE_QUERIES work class set and define the following work actions. Work action ONE_CONCURRENT_QUERY has a threshold action that allows one concurrent query to run on the system at a time for queries that fall within the LARGE_ESTIMATED_COST work class. If that threshold is exceeded, the database manager is to queue the activity, but is not to allow more than one database activity to be queued at a time. If the queue threshold is exceeded, the database activity is not to be allowed to run. Work action TWO_CONCURRENT_QUERIES has a threshold action that allows two concurrent queries to execute at the same time for queries that fall within the LARGE_CARDINALITY work class, and allows no more than two to be queued. If more than two queries are to be queued, the database activity is to continue putting the queries in the queue and is to collect the database activity data in the activities event monitor, if one is active.

   CREATE WORK ACTION SET DATABASE_ACTIONS
     FOR DATABASE USING WORK CLASS SET LARGE_QUERIES
       (WORK ACTION ONE_CONCURRENT_QUERY ON WORK CLASS LARGE_ESTIMATED_COST
        WHEN CONCURRENTDBCOORDACTIVITIES > 1 AND QUEUEDACTIVITIES > 1
          STOP EXECUTION,
        WORK ACTION TWO_CONCURRENT_QUERIES ON WORK CLASS LARGE_CARDINALITY
        WHEN CONCURRENTDBCOORDACTIVITIES > 2 AND QUEUEDACTIVITIES > 2
          COLLECT ACTIVITY DATA CONTINUE)

Example 2: Create a work action set named ADMIN_APPS_ACTIONS with one work action named MAP_SELECTS that is to apply to database activities that run under service superclass ADMIN_APPS. The work action is to map all database activity that falls within the SELECT_CLASS work class to service subclass SELECTS_SERVICE_CLASS, which is in the DML_SELECTS work class set.

   CREATE WORK ACTION SET ADMIN_APPS_ACTIONS
     FOR SERVICE CLASS ADMIN_APPS USING
       WORK CLASS SET DML_SELECTS
         (WORK ACTION MAP_SELECTS ON WORK CLASS SELECT_CLASS
          MAP ACTIVITY TO SELECTS_SERVICE_CLASS)