ALTER WORKLOAD statement

The ALTER WORKLOAD statement alters 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 at least one of the following authorities:
  • SQLADM authority, only if every alteration clause is a COLLECT clause
  • WLMADM authority
  • DBADM authority
To specify any clause other than a COLLECT clause, the authorization ID of the statement must include DBADM or WLMADM authority.

Syntax

Read syntax diagramSkip visual syntax diagramALTER WORKLOADworkload-name1ADDconnection-attributesDROPconnection-attributesALLOW DB ACCESSDISALLOW DB ACCESSENABLEDISABLEMAXIMUM DEGREE DEFAULTMAXIMUM DEGREEdegreeSERVICE CLASSservice-class-nameUNDERservice-superclass-namePOSITIONLASTBEFOREworkload-nameAFTERworkload-nameATpositionPRIORITYCRITICALHIGHMEDIUMLOWCOLLECT ACTIVITY DATAalter-collect-activity-data-clauseNONECOLLECT ACTIVITY METRICSBASENONEEXTENDEDCOLLECT AGGREGATE ACTIVITY DATABASEEXTENDEDNONECOLLECT AGGREGATE UNIT OF WORK DATABASENONECOLLECT LOCK TIMEOUT DATAalter-collect-history-clauseNONECOLLECT DEADLOCK DATAalter-collect-history-clauseCOLLECT LOCK WAIT DATAalter-collect-lock-wait-data-clauseNONECOLLECT UNIT OF WORK DATABASEBASEINCLUDE,PACKAGE LISTEXECUTABLE LISTNONEACTIVITY LIFETIME HISTOGRAM TEMPLATEtemplate-nameACTIVITY QUEUETIME HISTOGRAM TEMPLATEtemplate-nameACTIVITY EXECUTETIME HISTOGRAM TEMPLATEtemplate-nameACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATEtemplate-nameACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATEtemplate-nameUOW LIFETIME HISTOGRAM TEMPLATEtemplate-name
connection-attributes
Read syntax diagramSkip visual syntax diagram 2 ADDRESS('address-value')APPLNAME('application-name')SYSTEM_USER('authorization-name')SESSION_USER('authorization-name')SESSION_USER GROUP('authorization-name')SESSION_USER ROLE('authorization-name')CURRENT CLIENT_USERID('user-id')CURRENT CLIENT_APPLNAME('client-application-name')CURRENT CLIENT_WRKSTNNAME('workstation-name')CURRENT CLIENT_ACCTNG('accounting-string')CURRENT TENANT('tenant-name)
alter-collect-activity-data-clause
Read syntax diagramSkip visual syntax diagramON COORDINATORMEMBERON ALLMEMBERSWITHOUT DETAILSWITH,DETAILS3SECTIONINCLUDE ACTUALS BASEAND VALUES
alter-collect-history-clause
Read syntax diagramSkip visual syntax diagramWITHOUT HISTORYWITH HISTORYAND VALUES
alter-collect-lock-wait-data-clause
Read syntax diagramSkip visual syntax diagram●FOR LOCKS WAITING MORE THAN wait-timeSECONDSMICROSECONDS1 SECOND ●alter-collect-history-clause ●
Notes:
  • 1 The same clause must not be specified more than once.
  • 2 Each connection attribute clause can only be specified once.
  • 3 The DETAILS keyword is the minimum to be specified, followed by the option separated by a comma.

Description

workload-name
Identifies the workload that is to be altered. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The workload-name must identify a workload that exists at the current server (SQLSTATE 42704).
ADD connection-attributes
Adds one or more connection attribute values to the definition of the workload. Each specified connection attribute value must not already be defined for the workload (SQLSTATE 5U039). The ADD option cannot be specified if workload-name is 'SYSDEFAULTUSERWORKLOAD' or 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
DROP connection-attributes
Drops one or more connection attribute values from the definition of the workload. Each specified connection attribute value must be defined for the workload (SQLSTATE 5U040). The DROP option cannot be specified if workload-name is 'SYSDEFAULTUSERWORKLOAD' or 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832). There must be at least one defined connection attribute value. The last connection attribute value cannot be dropped (SQLSTATE 5U022).
connection-attributes
Specifies connection attribute values for the workload. 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 9.112.46.111. 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.128, 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.torolab.ibm.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). The authorization-name must identify a role that exists at the current server (SQLSTATE 42704).
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 (**).
CURRENT TENANT ('tenant-name', ...)
Specifies one or more tenant names for the CURRENT TENANT connection attribute. A tenant-name cannot appear more than once in the list (SQLSTATE 42713). The tenant-name must identify a tenant that exists at the current server (SQLSTATE 42704).
ALLOW DB ACCESS or DISALLOW DB ACCESS
Specifies whether or not a workload occurrence associated with this workload is allowed access to the database.
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. This option cannot be specified if workload-name is 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
ENABLE or DISABLE
Specifies whether or not this workload will be considered when a workload is chosen.
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. This option cannot be specified if workload-name is SYSDEFAULTUSERWORKLOAD or SYSDEFAULTADMWORKLOAD (SQLSTATE 42832).
MAXIMUM DEGREE
Specifies the maximum runtime degree of parallelism for this workload. The MAXIMUM DEGREE attribute can not be altered if workload-name is SYSDEFAULTADMWORKLOAD.
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 MAXIMUM DEGREE set on the query service class, 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 MAXIMUM DEGREE set on the query service class, the value set by SET RUNTIME DEGREE command and the SQL statement compilation degree.
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). This option cannot be specified if workload-name is 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
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. This option cannot be specified if workload-name is 'SYSDEFAULTUSERWORKLOAD' or 'SYSDEFAULTADMWORKLOAD' (SQLSTATE 42832).
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.
PRIORITY
Specifies the priority of the work from this workload compared to that of the work in other workloads in the same service superclass. Within a service superclass priority is used to prioritize more important jobs over less important jobs. Work scheduling across superclasses does not use the priority for scheduling, but instead uses only resource-based scheduling.
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.
alter-collect-activity-data-clause
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.
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 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. This data does not include SQL statements that are compiled by using the REOPT ALWAYS bind option.
NONE
Specifies that activity data is not collected for each activity that is associated with this workload.
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.

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 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 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:
  • 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 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 specified, is COLLECT AGGREGATE UNIT OF WORK DATA BASE.
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 data 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 any active locking event monitor when the lock event occurs. The lock timeout data is collected on all members. This setting works in conjunction with the MON_LOCKTIMEOUT database configuration setting. The setting that produces the most detailed output is honored.
alter-collect-history-clause
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.
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.
NONE
Specifies that lock timeout data for the workload is not collected at any member.
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. This setting is only honored if the MON_DEADLOCK database configuration parameter is not set to NONE.
alter-collect-history-clause
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.
WITH HISTORY
Specifies to collect past activity history in the current unit of work for all of these 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 LOCK WAIT DATA
Specifies that data about lock wait events that occur within this workload is sent to any active locking even monitor when the lock has not been acquired within wait-time. 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.
alter-collect-lock-wait-data-clause
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 the applicable 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.

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.
NONE
Specifies that the lock wait event for the workload is not collected at any member.
COLLECT UNIT OF WORK DATA
Specifies that data about each unit of work, also referred to as a transaction, associated with this workload is to be sent to the unit of work event monitors, if any have been created, when the unit of work ends. The default is COLLECT UNIT OF WORK 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.
BASE
Specifies that the 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.

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 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.
NONE
Specifies that no unit of work data for transactions associated with this workload is sent to the unit of work event monitor.
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. 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. 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. This information is collected only 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 running in the workload. This information is collected only 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 into this workload and the arrival of the next DML activity into this workload. 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. For newly submitted workload occurrences, changes take effect after the ALTER WORKLOAD statement commits. For active workload occurrences, changes take effect at the beginning of the next unit of work.
  • 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.
  • If the DISABLE option is specified, the workload is disabled after the statement commits. The workload is not considered the next time that a workload is chosen. If there is an active workload occurrence associated with this workload when the ALTER WORKLOAD statement commits, it continues to run until the end of the current unit of work. At the beginning of the next unit of work, a workload re-evaluation takes place, and the connection becomes associated with a different 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 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.
    • COLLECT UNIT OF WORK DATA PACKAGE LIST can be specified in place of COLLECT UNIT OF WORK DATA BASE INCLUDE PACKAGE LIST.
  • The workload association is re-evaluated at the beginning of each new unit of work if the database manager detects that 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.
    • The SET TENANT statement has been invoked and it changed the tenant with which this connection is associated.
  • After a service class, role, or tenant 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 remove the reference
    • Drop the workload

Examples

  • Example 1: The workload PAYROLL is currently positioned such that the workload INVENTORY is considered first when the database manager chooses a workload at run time. Alter the evaluation order so that PAYROLL will be considered first.
       ALTER WORKLOAD PAYROLL
     POSITION BEFORE INVENTORY
  • Example 2: Alter the evaluation order so that the workload BENCHMARK is evaluated by the database manager before any other workload in the catalog.
       ALTER WORKLOAD BENCHMARK
     POSITION AT 1
  • Example 3: The workload REPORTS was created with APPLNAME set to appl1, appl2, and appl3, and SYSTEM_USER set to BOB and MARY. Alter the workload to add a new application, appl4 to the application name list, and remove appl2, because it should no longer be mapped to REPORTS.
       ALTER WORKLOAD REPORTS
     ADD APPLNAME ('appl4')
     DROP APPLNAME ('appl2')
  • Example 4: Assuming a lock event monitor called LOCK exists and is active, create lock event records with statement history for lock timeout events that occur within the workload APP.
       ALTER WORKLOAD APP
     COLLECT LOCK TIMEOUT DATA WITH HISTORY
  • Example 5: Assuming a lock event monitor called LOCK exists and is active, create lock event records for only deadlock and lock timeout events that occur within the workload PAYROLL on all partitions. 
       ALTER WORKLOAD PAYROLL
     COLLECT DEADLOCK DATA
     COLLECT LOCK TIMEOUT DATA WITHOUT HISTORY
  • Example 6: Assuming a lock event monitor called LOCK exists and is active, create lock event records with statement history and values for deadlock events that occur within the workload INVOICE. 
       ALTER WORKLOAD INVOICE
     COLLECT DEADLOCK DATA WITH HISTORY AND VALUES
  • Example 7: Assuming a lock event monitor called LOCK exists and is active, create lock event records with statement history and values for locks acquired after waiting for more than 150 milliseconds that occur within the workload INVOICE.
       ALTER WORKLOAD INVOICE
     COLLECT LOCK WAIT DATA FOR LOCKS WAITING MORE THAN 150000
     MICROSECONDS WITH HISTORY AND VALUES
  • Example 8: Alter the workload REPORTS to collect unit of work data and send it to the unit of work event monitor:
       ALTER WORKLOAD REPORTS
     COLLECT UNIT OF WORK DATA BASE