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
>>-ALTER WORKLOAD--workload-name-------------------------------->
.-------------------------------------------------------------------------------------.
V (1) |
>----------+-ADD--| connection-attributes |--------------------------------------------+-+-><
+-DROP--| connection-attributes |-------------------------------------------+
+-+-ALLOW DB ACCESS----+----------------------------------------------------+
| '-DISALLOW DB ACCESS-' |
+-+-ENABLE--+---------------------------------------------------------------+
| '-DISABLE-' |
| .-MAXIMUM DEGREE DEFAULT-. |
+-+------------------------+------------------------------------------------+
| '-MAXIMUM DEGREE--degree-' |
+-SERVICE CLASS--service-class-name--+--------------------------------+-----+
| '-UNDER--service-superclass-name-' |
+-POSITION--+-LAST------------------+---------------------------------------+
| +-BEFORE--workload-name-+ |
| +-AFTER--workload-name--+ |
| '-AT--position----------' |
+-COLLECT ACTIVITY DATA--+-| alter-collect-activity-data-clause |-+---------+
| '-NONE-----------------------------------' |
| .-BASE-----. |
+-COLLECT ACTIVITY METRICS--+----------+------------------------------------+
| +-NONE-----+ |
| '-EXTENDED-' |
| .-BASE-----. |
+-COLLECT AGGREGATE ACTIVITY DATA--+----------+-----------------------------+
| +-EXTENDED-+ |
| '-NONE-----' |
| .-BASE-. |
+-COLLECT AGGREGATE UNIT OF WORK DATA--+------+-----------------------------+
| '-NONE-' |
+-COLLECT LOCK TIMEOUT DATA--+-| alter-collect-history-clause |-+-----------+
| '-NONE-----------------------------' |
+-COLLECT DEADLOCK DATA----| alter-collect-history-clause |-----------------+
+-COLLECT LOCK WAIT DATA--+-| alter-collect-lock-wait-data-clause |-+-------+
| '-NONE------------------------------------' |
| .-BASE---------------------------------------. |
+-COLLECT UNIT OF WORK DATA--+--------------------------------------------+-+
| +-BASE--+----------------------------------+-+ |
| | | .-,-------------------. | | |
| | | V | | | |
| | '-INCLUDE----+-PACKAGE LIST----+-+-' | |
| | '-EXECUTABLE LIST-' | |
| '-NONE---------------------------------------' |
+-ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name-----------------------+
+-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name----------------------+
+-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name--------------------+
+-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name------------------+
+-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name---------------+
'-UOW LIFETIME HISTOGRAM TEMPLATE--template-name----------------------------'
connection-attributes
.-----------------.
(2) 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'-+--)---------'
alter-collect-activity-data-clause
.-MEMBER-.
|--+-ON COORDINATOR--+--------+-+------------------------------->
| .-MEMBERS-. |
'-ON ALL--+---------+--------'
>--+-WITHOUT DETAILS-------------------------------------------------+--|
| .-,-------------------------------------. |
| V (3) | |
'-WITH----+-DETAILS---------------------------+-+--+------------+-'
'-SECTION--+----------------------+-' '-AND VALUES-'
'-INCLUDE ACTUALS BASE-'
alter-collect-history-clause
.-WITHOUT HISTORY--------------.
|--+------------------------------+-----------------------------|
'-WITH HISTORY--+------------+-'
'-AND VALUES-'
alter-collect-lock-wait-data-clause
|--●------------------------------------------------------------>
>--FOR LOCKS WAITING MORE THAN--+-wait-time--+-SECONDS------+-+-->
| '-MICROSECONDS-' |
'-1 SECOND--------------------'
>--●--| alter-collect-history-clause |--●-----------------------|
Notes:
- The same clause must not be specified more than once.
- 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
- 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).
- 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 (**).
- 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
- 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.
- 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.
- 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 DB2® 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 DB2 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 DB2 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.
Examples
- Example 1: The workload PAYROLL is currently positioned
such that the workload INVENTORY is considered first when DB2 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 DB2 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