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:
- 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-' |
+-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-----' |
+-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--+--------------+---------------------------+
| +-NONE---------+ |
| '-PACKAGE LIST-' |
+-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-----------'
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
.-DATABASE PARTITION-.
|--+-ON COORDINATOR--+--------------------+-+------------------->
| .-DATABASE PARTITIONS-. |
'-ON ALL--+---------------------+--------'
>--+-WITHOUT DETAILS---------------------------+----------------|
| .-,---------------. |
| V (3) | |
'-WITH----+-DETAILS-----+-+--+------------+-'
'-SECTION-----' '-AND VALUES-'
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.
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 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).
- 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 DATABASE PARTITION
- Specifies that activity data is to be collected
only at the database partition of the coordinator of the activity.
- ON ALL DATABASE PARTITIONS
- Specifies
that activity data is to be collected at all database partitions where
the activity is processed. On remote database partitions, a record
for the activity may be captured multiple times as the activity comes
and goes on those partitions. If the AND VALUES clause is specified,
activity input values will be collected only for the database partition
of the coordinator.
- WITHOUT DETAILS
- Specifies that data about each activity that
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. If section
actuals are enabled, they will be collected on any partition where
the activity data is collected.
- AND VALUES
- Specifies
that input data values are to be sent to any active activities event
monitor, for those activities that have them. This data does not include
SQL statements that are compiled by using the REOPT ALWAYS bind option.
- 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.
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 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:
- 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 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 partitions.
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 partition.
- 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 partitions. 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 partition.
- 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.
- NONE
- Specifies that no unit of work data for transactions associated
with this workload is sent to the unit of work event monitor.
- 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.
- 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 database partition 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.
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.
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