DB2 10.5 for Linux, UNIX, and Windows

CREATE WORKLOAD statement

The CREATE WORKLOAD statement defines a workload.

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 WORKLOAD--workload-name------------------------------->

   .---------------------------.                            
   V                           |                            
>----| connection-attributes |-+--| workload-attributes |------->

   .-POSITION LAST-----------------------.   
>--+-------------------------------------+---------------------->
   '-POSITION--+-BEFORE--workload-name-+-'   
               +-AFTER--workload-name--+     
               '-AT--position----------'     

   .-COLLECT ACTIVITY METRICS--NONE---------.   
>--+----------------------------------------+------------------->
   |                           .-BASE-----. |   
   '-COLLECT ACTIVITY METRICS--+----------+-'   
                               '-EXTENDED-'     

   .-COLLECT ACTIVITY DATA--NONE----------------------------------------------.   
>--+--------------------------------------------------------------------------+-->
   '-COLLECT ACTIVITY DATA--| collect-on-clause |--| collect-details-clause |-'   

   .-COLLECT AGGREGATE ACTIVITY DATA--NONE---------.   
>--+-----------------------------------------------+------------>
   |                                  .-BASE-----. |   
   '-COLLECT AGGREGATE ACTIVITY DATA--+----------+-'   
                                      '-EXTENDED-'     

   .-COLLECT AGGREGATE UNIT OF WORK DATA--NONE-----.   
>--+-----------------------------------------------+------------>
   |                                      .-BASE-. |   
   '-COLLECT AGGREGATE UNIT OF WORK DATA--+------+-'   

   .-COLLECT LOCK TIMEOUT DATA--WITHOUT HISTORY------------------.   
>--+-------------------------------------------------------------+-->
   '-COLLECT LOCK TIMEOUT DATA--+-NONE-------------------------+-'   
                                '-WITH HISTORY--+------------+-'     
                                                '-AND VALUES-'       

   .-COLLECT DEADLOCK DATA--WITHOUT HISTORY------------------.   
>--+---------------------------------------------------------+-->
   '-COLLECT DEADLOCK DATA----WITH HISTORY--+------------+---'   
                                            '-AND VALUES-'       

   .-COLLECT LOCK WAIT DATA--NONE--------------------------.   
>--+-------------------------------------------------------+---->
   '-COLLECT LOCK WAIT DATA--| collect-lock-wait-options |-'   

   .-COLLECT UNIT OF WORK DATA--NONE-------------------------------------------.   
>--+---------------------------------------------------------------------------+-->
   |                            .-BASE---------------------------------------. |   
   '-COLLECT UNIT OF WORK DATA--+--------------------------------------------+-'   
                                '-BASE--+----------------------------------+-'     
                                        |          .-,-------------------. |       
                                        |          V                     | |       
                                        '-INCLUDE----+-PACKAGE LIST----+-+-'       
                                                     '-EXECUTABLE LIST-'           

>--| histogram-template-clause |-------------------------------><

connection-attributes

                       .-----------------.                                  
    (1)                V                 |                                  
|--------+-ADDRESS--(----'address-value'-+--)---------------------------+--|
         |              .--------------------.                          |   
         |              V                    |                          |   
         +-APPLNAME--(----'application-name'-+--)-----------------------+   
         |                 .----------------------.                     |   
         |                 V                      |                     |   
         +-SYSTEM_USER--(----'authorization-name'-+--)------------------+   
         |                  .----------------------.                    |   
         |                  V                      |                    |   
         +-SESSION_USER--(----'authorization-name'-+--)-----------------+   
         |                        .----------------------.              |   
         |                        V                      |              |   
         +-SESSION_USER GROUP--(----'authorization-name'-+--)-----------+   
         |                       .----------------------.               |   
         |                       V                      |               |   
         +-SESSION_USER ROLE--(----'authorization-name'-+--)------------+   
         |                           .-----------.                      |   
         |                           V           |                      |   
         +-CURRENT CLIENT_USERID--(----'user-id'-+--)-------------------+   
         |                             .---------------------------.    |   
         |                             V                           |    |   
         +-CURRENT CLIENT_APPLNAME--(----'client-application-name'-+--)-+   
         |                               .--------------------.         |   
         |                               V                    |         |   
         +-CURRENT CLIENT_WRKSTNNAME--(----'workstation-name'-+--)------+   
         |                           .---------------------.            |   
         |                           V                     |            |   
         '-CURRENT CLIENT_ACCTNG--(----'accounting-string'-+--)---------'   

workload-attributes

   .-ENABLE--.  .-ALLOW DB ACCESS----.   
|--+---------+--+--------------------+-------------------------->
   '-DISABLE-'  '-DISALLOW DB ACCESS-'   

   .-MAXIMUM DEGREE DEFAULT-.   
>--+------------------------+----------------------------------->
   '-MAXIMUM DEGREE--degree-'   

   .-SERVICE CLASS SYSDEFAULTUSERCLASS-------------------------------------.   
>--+-----------------------------------------------------------------------+--|
   '-SERVICE CLASS--service-class-name--+--------------------------------+-'   
                                        '-UNDER--service-superclass-name-'     

collect-on-clause

                     .-MEMBER-.     
   .-ON COORDINATOR--+--------+-.   
|--+----------------------------+-------------------------------|
   |         .-MEMBERS-.        |   
   '-ON ALL--+---------+--------'   

collect-details-clause

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

collect-lock-wait-options

|--●------------------------------------------------------------>

>--FOR LOCKS WAITING MORE THAN--+-wait-time--+-SECONDS------+-+-->
                                |            '-MICROSECONDS-' |   
                                '-1 SECOND--------------------'   

      .-WITHOUT HISTORY--------------.      
>--●--+------------------------------+--●-----------------------|
      '-WITH HISTORY--+------------+-'      
                      '-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------'   

   .-UOW LIFETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.   
>--+-----------------------------------------------------+------|
   '-UOW LIFETIME HISTOGRAM TEMPLATE--template-name------'

Notes:

Each connection attribute clause can only be specified once.
The DETAILS keyword is the minimum to be specified, followed by the option separated by a comma.

Description

workload-name

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

connection-attributes

The attributes of the connection must match all attributes specified in this workload definition if it is to be associated with this workload when the connection is established. If a list of values is specified for a connection attribute in the workload definition, the corresponding attribute of the connection must match at least one of the values in the list. If a connection attribute is not specified in the workload definition, the connection can have any value for the corresponding connection attribute.

Note: All connection attributes are case sensitive, except for ADDRESS.

ADDRESS ('address-value', ...)

Specifies one or more IPv4 addresses, IPv6 addresses, or secure domain names for the ADDRESS connection attribute. An address value cannot appear more than once in the list (SQLSTATE 42713). The only supported protocol is TCP/IP. Each address value must be an IPv4 address, an IPv6 address, or a secure domain name.

An IPv4 address must not contain leading spaces and is represented as a dotted decimal address. An example of an IPv4 address is 192.0.2.1. The value localhost or its equivalent representation 127.0.0.1 will not result in a match; the real IPv4 address of the host must be specified instead. An IPv6 address must not contain leading spaces and is represented as a colon hexadecimal address. An example of an IPv6 address is 2001:0DB8:0000:0000:0008:0800:200C:417A. IPv4-mapped IPv6 addresses (::ffff:192.0.2.1, for example) will not result in a match. Similarly, localhost or its IPv6 short representation ::1 will not result in a match. A domain name is converted to an IP address by the domain name server where a resulting IPv4 or IPv6 address is determined. An example of a domain name is corona.example.com. When a domain name is converted to an IP address, the result of this conversion could be a set of one or more IP addresses. In this case, an incoming connection is said to match the ADDRESS attribute of a workload object if the IP address from which the connection originates matches any of the IP addresses to which the domain name was converted.

When creating a workload object, you should specify domain name values for the ADDRESS attribute instead of static IP addresses, particularly in Dynamic Host Configuration Protocol (DHCP) environments where a device can have a different IP address each time it connects to the network.

APPLNAME ('application-name', ...)

Specifies one or more applications for the APPLNAME connection attribute. An application name cannot appear more than once in the list (SQLSTATE 42713). If application-name does not contain a single asterisk character (*), is equivalent to the value shown in the "Application name" field in system monitor output and in output from the LIST APPLICATIONS command. If application-name does contain a single asterisk character (*), the value is used as an expression to represent a set of application names, where the asterisk (*) represents a string of zero or more characters. If the expression needs to include an asterisk character in the application name, use a sequence of two asterisk characters (**).

SYSTEM_USER ('authorization-name', ...)

Specifies one or more authorization IDs for the SYSTEM USER connection attribute. An authorization ID cannot appear more than once in the list (SQLSTATE 42713).

SESSION_USER ('authorization-name', ...)

Specifies one or more authorization IDs for the SESSION USER connection attribute. An authorization ID cannot appear more than once in the list (SQLSTATE 42713).

SESSION_USER GROUP ('authorization-name', ...)

Specifies one or more authorization IDs for the SESSION_USER GROUP connection attribute. An authorization ID cannot appear more than once in the list (SQLSTATE 42713).

SESSION_USER ROLE ('authorization-name', ...)

Specifies one or more authorization IDs for the SESSION_USER ROLE connection attribute. The roles of a session authorization ID in this context refer to all the roles that are available to the session authorization ID, regardless of how the roles were obtained. An authorization ID cannot appear more than once in the list (SQLSTATE 42713).

CURRENT CLIENT_USERID ('user-id', ...)

Specifies one or more client user IDs for the CURRENT CLIENT_USERID connection attribute. A client user ID cannot appear more than once in the list (SQLSTATE 42713). If user-id contains a single asterisk character (*), the value is used as an expression to represent a set of user IDs, where the asterisk (*) represents a string of zero or more characters. If the expression needs to include an asterisk character in the user ID, use a sequence of two asterisk characters (**).

CURRENT CLIENT_APPLNAME ('client-application-name', ...)

Specifies one or more applications for the CURRENT CLIENT_APPLNAME connection attribute. An application name cannot appear more than once in the list (SQLSTATE 42713). If client-application-name does not contain a single asterisk character (*), is equivalent to the value shown in the "TP Monitor client application name" field in system monitor output. If client-application-name does contain a single asterisk character (*), the value is used as an expression to represent a set of application names, where the asterisk (*) represents a string of zero or more characters. If the expression needs to include an asterisk character in the application name, use a sequence of two asterisk characters (**).

CURRENT CLIENT_WRKSTNNAME ('workstation-name', ...)

Specifies one or more client workstation names for the CURRENT CLIENT_WRKSTNNAME connection attribute. A client workstation name cannot appear more than once in the list (SQLSTATE 42713). If workstation-name contains a single asterisk character (*), the value is used as an expression to represent a set of workstation names, where the asterisk (*) represents a string of zero or more characters. If the expression needs to include an asterisk character in the workstation name, use a sequence of two asterisk characters (**).

CURRENT CLIENT_ACCTNG ('accounting-string', ...)

Specifies one or more client accounting strings for the CURRENT CLIENT_ACCTNG connection attribute. A client accounting string cannot appear more than once in the list (SQLSTATE 42713). If accounting-string contains a single asterisk character (*), the value is used as an expression to represent a set of accounting strings, where the asterisk (*) represents a string of zero or more characters. If the expression needs to include an asterisk character in the accounting string, use a sequence of two asterisk characters (**).

workload-attributes

Specifies attributes of the workload.

ENABLE or DISABLE

Specifies whether or not this workload will be considered when a workload is chosen. The default is ENABLE.

ENABLE: Specifies that the workload is enabled and will be considered when a workload is chosen.
DISABLE: Specifies that the workload is disabled and will not be considered when a workload is chosen.

ALLOW DB ACCESS or DISALLOW DB ACCESS

Specifies whether or not a workload occurrence associated with this workload is allowed access to the database. The default is ALLOW DB ACCESS.

ALLOW DB ACCESS: Specifies that workload occurrences associated with this workload are allowed access to the database.
DISALLOW DB ACCESS: Specifies that workload occurrences associated with this workload are not allowed access to the database. The next unit of work associated with this workload will be rejected (SQLSTATE 5U020). Workload occurrences that are already running are allowed to complete.

MAXIMUM DEGREE

Specifies the maximum runtime degree of parallelism for this workload. The default is DEFAULT.

DEFAULT: If DB2_WORKLOAD=ANALYTICS, this setting enables intrapartition parallelism for this workload. Otherwise, this setting specifies that this workload inherits the intrapartition parallelism setting from the database manager configuration parameter intra_parallel. When intra_parallel is set to NO, this workload runs with intrapartition parallelism disabled. When intra_parallel is set to YES, this workload runs with intrapartition parallelism enabled. This workload does not specify a maximum runtime degree for assigned applications. Therefore, the actual runtime degree is determined as the lower of the value of max_querydegree configuration parameter, the value set by SET RUNTIME DEGREE command, and the SQL statement compilation degree.
degree: Specifies the maximum degree of parallelism for this workload. Valid values are 1 to 32,767. With value 1, the associated requests run with intrapartition parallelism disabled. With value 2 to 32,767, the associated requests run with intrapartition parallelism enabled. The actual runtime degree is determined as the lower of this degree, the value of max_querydegree configuration parameter, the value set by SET RUNTIME DEGREE command and the SQL statement compilation degree.
Note: A MAXIMUM DEGREE value greater than 1 will not enable intrapartition parallelism unless the shared sort heap is available.

SERVICE CLASS service-class-name

Specifies that requests associated with this workload are to be executed in the service class service-class-name. The service-class-name must identify a service class that exists at the current server (SQLSTATE 42704). The service-class-name cannot be 'SYSDEFAULTSUBCLASS', 'SYSDEFAULTSYSTEMCLASS', or 'SYSDEFAULTMAINTENANCECLASS' (SQLSTATE 5U032). The default is SYSDEFAULTUSERCLASS.

UNDER service-superclass-name: This clause is used when specifying a service subclass. The service-superclass-name identifies the service superclass of service-class-name. The service-superclass-name must identify a service superclass that exists at the current server (SQLSTATE 42704). The service-superclass-name cannot be 'SYSDEFAULTSYSTEMCLASS' or 'SYSDEFAULTMAINTENANCECLASS' (SQLSTATE 5U032).

POSITION

Specifies where this workload is to be placed within the ordered list of workloads. At run time, this list is searched in order for the first workload that matches the required connection attributes. The default is LAST.

LAST: Specifies that the workload is to be last in the list, before the default workloads SYSDEFAULTUSERWORKLOAD and SYSDEFAULTADMWORKLOAD.
BEFORE relative-workload-name: Specifies that the workload is to be placed before workload relative-workload-name in the list. The relative-workload-name must identify a workload that exists at the current server (SQLSTATE 42704). The BEFORE option cannot be specified if relative-workload-name is 'SYSDEFAULTUSERWORKLOAD' or 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
AFTER relative-workload-name: Specifies that the workload is to be placed after workload relative-workload-name in the list. The relative-workload-name must identify a workload that exists at the current server (SQLSTATE 42704). The AFTER option cannot be specified if relative-workload-name is 'SYSDEFAULTUSERWORKLOAD' or 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
AT position: Specifies the absolute position at which the workload is to be placed in the list. This value can be any positive integer (not zero) (SQLSTATE 42615). If position is greater than the number of existing workloads plus one, the workload is placed at the last position, just before SYSDEFAULTUSERWORKLOAD and SYSDEFAULTADMWORKLOAD.

COLLECT ACTIVITY METRICS

Specifies that monitor metrics should be collected for an activity submitted by an occurrence of the workload. The default is COLLECT ACTIVITY METRICS NONE.

Note: The effective activity metrics collection setting is the combination of the attribute specified by the COLLECT ACTIVITY METRICS clause on the workload submitting the activity, and the mon_act_metrics database configuration parameter. If either the workload attribute or the configuration parameter has a value other than NONE, metrics will be collected for the activity.

NONE

Specifies that no metrics will be collected for any activity submitted by an occurrence of the workload.

BASE

Specifies that basic metrics will be collected for any activity submitted by an occurrence of the workload.

EXTENDED

Specifies that basic metrics will be collected for any activity submitted by an occurrence of the workload. In addition, specifies that the values for the following monitor elements should be determined with additional granularity:

total_section_time
total_section_proc_time
total_routine_user_code_time
total_routine_user_code_proc_time
total_routine_time

COLLECT ACTIVITY DATA

Specifies that data about each activity associated with this workload is to be sent to any active activities event monitor when the activity completes. The default is COLLECT ACTIVITY DATA NONE.

collect-on-clause

Specifies where the activity data is to be collected. The default is ON COORDINATOR MEMBER.

ON COORDINATOR MEMBER: Specifies that 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.

NONE

Specifies that activity data is not collected for each activity that is associated with this workload.

collect-details-clause

Specifies what type of activity data is to be collected. The default is WITHOUT DETAILS.

WITHOUT DETAILS

Specifies that data about each activity that is associated with this workload is to 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 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. 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 about the activities associated with this workload is to be 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 when COLLECT AGGREGATE ACTIVITY DATA is not specified is COLLECT AGGREGATE ACTIVITY DATA NONE. The default when COLLECT AGGREGATE ACTIVITY DATA is specified is COLLECT AGGREGATE ACTIVITY DATA BASE.

BASE

Specifies that basic aggregate activity data about the activities associated with this workload is to be sent to the statistics event monitor, if one is active. Basic aggregate activity data includes:

Activity CPU time high watermark
Activity execution time histogram
Activity life time histogram
Activity queue time histogram
Activity rows read high watermark
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.

EXTENDED

Specifies that all aggregate activity data about the activities associated with this workload is to be 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

NONE

Specifies that no aggregate activity data is to be collected for this workload.

COLLECT AGGREGATE UNIT OF WORK DATA

Specifies that aggregate unit of work data about the units of work associated with this workload is to be 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 when COLLECT AGGREGATE UNIT OF WORK DATA is not specified is COLLECT AGGREGATE UNIT OF WORK DATA NONE.

BASE

Specifies that basic aggregate unit of work data about the units of work associated with this workload is to be sent to the statistics event monitor, if one is active. Basic aggregate unit of work includes:

Unit of work lifetime histogram

NONE

Specifies that no aggregate unit of work data is to be collected for this workload.

COLLECT LOCK TIMEOUT DATA

Specifies that data about lock timeout events that occur within this workload is sent to the applicable event monitor when the lock event occurs. The lock timeout data is collected on all members. The default is COLLECT LOCK TIMEOUT DATA WITHOUT HISTORY. This setting works in conjunction with the mon_locktimeout database configuration parameter setting. The setting that produces the most detailed output is honored.

WITHOUT HISTORY

Specifies that data about lock events that occur within this workload is sent to any active locking event monitor when the lock event occurs. Past activity history and input values are not sent to the event monitor.

NONE

Specifies that lock timeout data for the workload is not collected at any member.

WITH HISTORY

Specifies to collect past activity history in the current unit of work for all of this type of lock events. The activity history buffer will wrap after the maximum size limit is used.

The default limit on the number of past activities to be kept by any one application is 250. If the number of past activities is greater than the limit, only the newest activities are reported. This default value can be overridden using the registry variable DB2_MAX_INACT_STMTS to specify a different value. You can choose a different value for the limit to increase or reduce the amount of system monitor heap used for past activity information.

AND VALUES: Specifies that input data values are to be sent to any active locking event monitor for those activities that have them. These data values will not include LOB data, LONG VARCHAR data, LONG VARGRAPHIC data, structured type data, or XML data. For SQL statements compiled using the REOPT ALWAYS bind option, there will be no REOPT compilation or statement execution data values provided in the event information.

COLLECT DEADLOCK DATA

Specifies that data about deadlock events that occur within this workload is sent to any active locking event monitor when the lock event occurs. The deadlock data is collected on all members. The default is COLLECT DEADLOCK DATA WITHOUT HISTORY. This setting is only honored if the mon_deadlock database configuration parameter is not set to NONE.

WITHOUT HISTORY

WITH HISTORY

Specifies to collect past activity history in the current unit of work for all of this type of lock events. The activity history buffer will wrap after the maximum size limit is used.

AND VALUES: Specifies that input data values are to be sent to any active locking event monitor for those activities that have them. These data values will not include LOB data, LONG VARCHAR data, LONG VARGRAPHIC data, structured type data, or XML data. For SQL statements compiled using the REOPT ALWAYS bind option, there will be no REOPT compilation or statement execution data values provided in the event information.

COLLECT LOCK WAIT DATA

Specifies that data about lock wait events that occur within this workload is sent to any active locking event monitor when the lock has not been acquired within wait-time. The default is COLLECT LOCK WAIT DATA NONE with a default wait-time value of 0 microseconds. This setting works in conjunction with the mon_lockwait and mon_lw_thresh database configuration parameters. The setting that produces the most detailed output is honored.

NONE

Specifies that the lock wait event for the workload is not collected at any member.

FOR LOCKS WAITING MORE THAN wait-time (SECONDS | MICROSECONDS) | 1 SECOND

Specifies that data about lock wait events that occur within this workload is sent to any active locking event monitor when the lock has not been acquired within wait-time.

This value can be any non-negative integer. Use a valid duration keyword to specify an appropriate unit of time for wait-time. The minimum valid value for the wait-time parameter is 1000 microseconds.

WITH HISTORY

Specifies to collect past activity history in the current unit of work for all of this type of lock events. The activity history buffer will wrap after the maximum size limit is used.

AND VALUES: Specifies that input data values are to be sent to any active locking event monitor for those activities that have them. These data values will not include LOB data, LONG VARCHAR data, LONG VARGRAPHIC data, structured type data, or XML data. For SQL statements compiled using the REOPT ALWAYS bind option, there will be no REOPT compilation or statement execution data values provided in the event information.

COLLECT UNIT OF WORK DATA

Specifies that data about each transaction associated with this workload is to be sent to the unit of work event monitor, if any are active, when the unit of work ends. The default, when COLLECT UNIT OF WORK DATA is not specified, is COLLECT UNIT OF WORK DATA NONE. The default, when COLLECT UNIT OF WORK DATA is specified, is COLLECT UNIT OF WORK DATA BASE. If the mon_uow_data database configuration parameter is set to BASE, it takes precedence over the COLLECT UNIT OF WORK DATA parameter. A value of NONE for the mon_uow_data indicates that the COLLECT UNIT OF WORK DATA parameters of individual workloads is used.

NONE

Specifies that no unit of work data for transactions associated with this workload is sent to the unit of work event monitor. The default is COLLECT UNIT OF WORK DATA NONE.

BASE

Specifies that base level of data for transactions associated with this workload is sent to the unit of work event monitors.

Some of the information reported in a unit of work event are system level request metrics. The collection of these metrics is controlled independently from the collection of the unit of work data. The request metrics are controlled with the COLLECT REQUEST METRICS clause on superclass, or using the mon_req_metrics database configuration parameter. The service super class which the workload is associated with, or the service super class of the service subclass which the workload is associated with, must have the collection of request metrics enabled in order for the request metrics to be present in the unit of work event. If the request metrics collection is not enabled, the value of the request metrics will be zero.

INCLUDE PACKAGE LIST

Specifies that base level of data and the package list for transactions associated with this workload are sent to the unit of work event monitor.

The size of the collected package list is determined by the value of the mon_pkglist_sz database configuration parameter. If this value is 0, then the package list is not collected even if the PACKAGE LIST option is specified.

In a partitioned database environment, the package list is only available on the coordinator member. The BASE level will be collected on remote members.

INCLUDE EXECUTABLE LIST

Specifies that executable ID list will be collected for a unit of work together with base level of data and sent to the unit of work event monitor.

histogram-template-clause

Specifies the histogram templates to use when collecting aggregate activity data for activities executing in the workload.

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 in the workload during a specific interval. This time includes both time queued and time executing. The default is SYSDEFAULTHISTOGRAM. This information is collected only 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 running in the workload are queued during a specific interval. The default is SYSDEFAULTHISTOGRAM. This information is collected only 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 running in the workload are executing during a specific interval. This time does not include the time spent queued. Activity execution time is collected in this histogram at the coordinator member only. The time does not include idle time. Idle time is the time between the execution of requests belonging to the same activity when no work is being done. An example of idle time is the time between the end of opening a cursor and the start of fetching from that cursor. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified, with either the BASE or EXTENDED option. Only activities at nesting level 0 are considered for inclusion in the histogram.
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 running in the workload. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED option. Only activities at nesting level 0 are considered for inclusion in the histogram.
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 into this workload and the arrival of the next DML activity into this workload. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED option.
UOW LIFETIME HISTOGRAM TEMPLATE template-name: Specifies the template that describes the histogram used to collect statistical data about the duration, in milliseconds, of units of work running in the workload during a specific interval. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE UNIT OF WORK DATA clause is specified with the BASE 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.
When a database connection is established, the database manager looks for a matching workload based on the connection attributes that were specified in the POSITION clause (in order of specification). If a matching workload is found, the database manager checks whether the current session user has USAGE privilege on that workload. If the session user does not have USAGE privilege on the workload, the database manager looks for the next matching workload. If the session user has USAGE privilege on this workload, the connection is associated with the workload. If a matching workload is not found, the connection is associated with the default user workload, SYSDEFAULTUSERWORKLOAD. If the session user does not have USAGE privilege on SYSDEFAULTUSERWORKLOAD, an error is returned (SQLSTATE 42501).
The workload association is re-evaluated at the beginning of each new unit of work if the database manager detects one of the following conditions.
- The connection attributes have changed. This can happen if any of the following events has occurred:
  - The set client information API (sqleseti) has been invoked and it changed the connection attributes that were included in the workload definition. Note that although the client information can be set by the end user so that it could initiate a workload re-evaluation, the workload remapping itself cannot happen if the session user does not have the USAGE privilege on the workload.
  - The SET SESSION AUTHORIZATION statement has been invoked and it changed the current session user.
  - The roles that are available to a session user have changed.
- A workload is created.
- A workload is dropped.
- A workload is altered.
- The USAGE privilege on a workload is granted to a user, group, or role.
- The USAGE privilege on a workload is revoked from a user, group, or role.
If the workload re-evaluation results in no workload reassignment, the current workload occurrence continues to run; that is, a new workload occurrence will not be started.
A connection cannot be reassigned to a different workload when an activity is still active. Examples of such activities are a load operation, an executing procedure, or statements that maintain resources across multiple units of work, such as an open WITH HOLD cursor. The current workload occurrence continues to run until all executing activities complete. Workload reassignment occurs at the beginning of the next unit of work.
After a service class has been referenced by a workload, it cannot be dropped until it is no longer referenced by any workload. Either of the following actions can be taken to remove a service class reference from a workload:
- Alter the workload to change the service class name
- Drop the workload
After a role has been referenced by a workload, it cannot be dropped until it is no longer referenced by any workload. Either of the following actions can be taken to remove a role reference from a workload:
- Alter the workload to remove the role
- Drop the workload
Privileges: The USAGE privilege is not granted to any user, group, or role when a workload is created. To enable use of a workload, grant USAGE privilege on that workload to a user, a group, or a role using the GRANT USAGE ON WORKLOAD statement.
Syntax alternatives: The following 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.
- COLLECT UNIT OF WORK DATA PACKAGE LIST can be specified in place of COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST.

Examples

Example 1: Create a workload named CAMPAIGN for requests that are submitted by a session user belonging to group FINANCE. These requests are to be executed in the default user service class SYSDEFAULTUSERCLASS.
```
   CREATE WORKLOAD CAMPAIGN
     SESSION_USER GROUP ('FINANCE')
```
Example 2: Create a workload named PAYROLL for a session user with role HR that has the CURRENT CLIENT_APPLNAME special register set to SALARYSYS. Units of work associated with this workload are to be executed in service class MEDIUMSC that is under the service superclass HRSC. When a workload is chosen at run time, this workload should be evaluated only after the workload CAMPAIGN has been evaluated and determined to not match.
```
   CREATE WORKLOAD PAYROLL
     SESSION_USER ROLE ('HR')
     CURRENT CLIENT_APPLNAME ('SALARYSYS') SERVICE CLASS MEDIUMSC
     UNDER HRSC POSITION AFTER CAMPAIGN
```
Example 3: An occurrence of workload CAMPAIGN (from example 1) is currently running on the system. Create a workload named NEWCAMPAIGN, also for requests that are submitted by a session user belonging to group FINANCE, but only those requests submitted through application DB2BP.EXE. Requests associated with this workload are to be executed in service class MARKETINGSC. NEWCAMPAIGN should be evaluated before CAMPAIGN.
```
   CREATE WORKLOAD NEWCAMPAIGN
     SESSION_USER GROUP ('FINANCE')
     APPLNAME ('DB2BP.EXE') SERVICE CLASS MARKETINGSC
     POSITION BEFORE CAMPAIGN
```
The running workload occurrence of CAMPAIGN continues to run until the current unit of work completes, at which time a workload re-evaluation takes place, and the connection could then be remapped to workload NEWCAMPAIGN.
Example 4: Create a workload named REPORTS for requests that are submitted through application appl1, appl2, or appl3 by system user BOB or MARY.
```
   CREATE WORKLOAD REPORTS
     APPLNAME ('appl1', 'appl2', 'appl3')
     SYSTEM_USER ('BOB', 'MARY')
```
Example 5: Assuming a lock event monitor called PAYROLL exists and is active, create lock event records with statement history for lock timeout events that occur within the workload EMPLOYEES.
```
   CREATE WORKLOAD EMPLOYEES
     APPLNAME ("app1", "app2")
     COLLECT LOCK TIMEOUT DATA WITH HISTORY
```
Example 6: Assuming a lock event monitor called PAYROLL exists and is active, create lock event records for only deadlock and lock timeout events that occur within the workload FINANCE on all partitions.
```
   CREATE WORKLOAD FINANCE
     APPLNAME ("app1", "app2")
     COLLECT DEADLOCK DATA
     COLLECT LOCK TIMEOUT DATA
```
Example 7: Assuming a lock event monitor called PAYROLL exists and is active, create lock event records with statement history and values for deadlock events that occur within the workload MANAGERS.
```
   CREATE WORKLOAD MANAGERS
     APPLNAME ("app1", "app2")
     COLLECT DEADLOCK DATA WITH HISTORY AND VALUES
```
Example 8: Assuming a lock event monitor called PAYROLL exists and is active, create lock event records with statement history for locks that are acquired after waiting 5000 milliseconds within the MANAGERS workload.
```
   CREATE WORKLOAD MANAGERS
     APPLNAME ("app1", "app2")
     COLLECT LOCK WAIT DATA FOR LOCKS WAITING MORE THAN 5 SECONDS WITH HISTORY
```
Example 9: Create a workload named ACCRECS for all accounts receivable applications that share a similar name (accrec01, accrec02 ... accrec15) and assign them to the service class ACCOUNTNGSC. Application names are identified through the APPLNAME connection attribute with the help of a wild card (*) and do not need to be specified individually.
```
   CREATE WORKLOAD ACCRECS
     SESSION_USER GROUP ('ACCOUNTING')
     APPLNAME ('accrec*')
     SERVICE CLASS ACCOUNTNGSC
```
Example 10: Create a workload named CAMPAIGN for requests submitted through the application appl1, and have unit of work data collected and sent to any active unit of work event monitors.
```
   CREATE WORKLOAD CAMPAIGN
     APPLNAME ('appl1')
     COLLECT UNIT OF WORK DATA BASE
```
Example 11: The following statements show how you can specify the different address value formats supported by the ADDRESS connection attribute when creating a workload.
- To specify a secure domain name:
```
 CREATE WORKLOAD DOMAINWORKLOAD
   ADDRESS ('aviator.example.com')
```
- To specify a IPv4 address value:
```
 CREATE WORKLOAD IPWORKLOAD1
   ADDRESS ('192.0.2.11')
```
- To specify a IPv6 address value (long format):
```
 CREATE WORKLOAD IPWORKLOAD2
   ADDRESS ('2001:db8:519:13:204:acff:fe57:6135')
```
- To specify a IPv6 address value (short format):
```
 CREATE WORKLOAD IPWORKLOAD3
   ADDRESS ('2001:db8::202:55ff:fe9a:6eee')
```