The ALTER SERVICE CLASS statement alters the definition
of a service class.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. 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
Syntax
>>-ALTER SERVICE CLASS--service-class-name---------------------->
>--+--------------------------------+--------------------------->
'-UNDER--service-superclass-name-'
.---------------------------------------------------------------------------------.
V (1) .-HARD-. |
>--------+-+------+--CPU SHARES--integer-constant----------------------------------+-+-><
| '-SOFT-' |
+-CPU LIMIT--+-integer-constant-+-----------------------------------------+
| '-NONE-------------' |
+-PREFETCH PRIORITY--+-DEFAULT-+------------------------------------------+
| +-HIGH----+ |
| +-MEDIUM--+ |
| '-LOW-----' |
+-OUTBOUND CORRELATOR--+-NONE------------+--------------------------------+
| '-string-constant-' |
+-BUFFERPOOL PRIORITY--+-DEFAULT-+----------------------------------------+
| +-HIGH----+ |
| +-MEDIUM--+ |
| '-LOW-----' |
| (2) |
+-------COLLECT ACTIVITY DATA--+-| alter-collect-activity-data-clause |-+-+
| '-NONE-----------------------------------' |
| .-BASE-----. |
+-COLLECT AGGREGATE ACTIVITY DATA--+----------+---------------------------+
| +-EXTENDED-+ |
| '-NONE-----' |
| .-BASE-. |
+-COLLECT AGGREGATE REQUEST DATA--+------+--------------------------------+
| '-NONE-' |
| .-BASE-. |
+-COLLECT AGGREGATE UNIT OF WORK DATA--+------+---------------------------+
| '-NONE-' |
| (3) .-BASE-----. |
+-------COLLECT REQUEST METRICS--+----------+-----------------------------+
| +-NONE-----+ |
| '-EXTENDED-' |
| (4) |
+-------ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name---------------+
+-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name--------------------+
+-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name------------------+
+-REQUEST EXECUTETIME HISTOGRAM TEMPLATE--template-name-------------------+
+-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name----------------+
+-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name-------------+
+-UOW LIFETIME HISTOGRAM TEMPLATE--template-name--------------------------+
'-+-ENABLE--+-------------------------------------------------------------'
'-DISABLE-'
alter-collect-activity-data-clause
.-MEMBER-.
|----+-ON COORDINATOR--+--------+-+----------------------------->
| .-MEMBERS-. |
'-ON ALL--+---------+--------'
>--+-WITHOUT DETAILS-------------------------------------------------+--|
| .-,-------------------------------------. |
| V (5) | |
'-WITH----+-DETAILS---------------------------+-+--+------------+-'
'-SECTION--+----------------------+-' '-AND VALUES-'
'-INCLUDE ACTUALS BASE-'
Notes:
- The same clause must not be specified more than once.
- All COLLECT clauses except for COLLECT REQUEST METRICS are only valid
for a service subclass.
- The COLLECT REQUEST METRICS clause is only valid for a service
superclass.
- The
HISTOGRAM TEMPLATE clauses are only valid for a service subclass.
- The DETAILS keyword
is the minimum to be specified, followed by the option separated by
a comma.
Description
- service-class-name
- Identifies the service class that is to be altered. This is
a one-part name. It is an SQL identifier (either ordinary or delimited).The service-class-name must
identify a service class that exists in the database (SQLSTATE 42704).
To alter a service subclass, the service-superclass-name must
be specified using the UNDER clause.
- UNDER service-superclass-name
- This clause is used only for altering a service subclass. The service-superclass-name identifies
the service superclass of the service subclass and must identify a
service superclass that exists in the database (SQLSTATE 42704).
- SOFT CPU SHARES integer-constant or
HARD CPU SHARES integer-constant
- Specifies
the number of shares of CPU resources that the workload manager (WLM)
dispatcher allocates to this service class when work is executing
within this service class. Valid values for the integer-constant are
integers between 1 and 65535. Qualifying CPU SHARES with
the keyword HARD, or specifying CPU
SHARES without qualifying it with the keyword HARD or SOFT,
indicates that hard CPU shares are to be
allocated to this service class. Specifying the keyword SOFT indicates
that soft CPU shares are to be allocated
to this service class. To use hard and
soft CPU shares with WLM dispatcher, you must enable the wlm_disp_cpu_shares database
manager configuration parameter.
- CPU LIMIT integer-constant or CPU LIMIT
NONE
- Specifies the maximum percentage of the CPU resources that the
WLM dispatcher can assign to this service class. Valid values for
the integer-constant are integers between 1 and
100. You can also specify CPU LIMIT NONE to indicate that there is
no CPU limit.
- PREFETCH PRIORITY DEFAULT | HIGH | MEDIUM | LOW
- This parameter controls the priority with which agents in the
service class can submit their prefetch requests. Valid values are
HIGH, MEDIUM, LOW, or DEFAULT (SQLSTATE 42615). HIGH, MEDIUM, and
LOW mean that prefetch requests will be submitted to the high, medium,
and low priority queues, respectively. Prefetchers empty the priority
queue in order from high to low. Agents in the service class submit
their prefetch requests at the PREFETCH PRIORITY level when the next
activity begins. If PREFETCH PRIORITY is altered after a prefetch
request is submitted, the request priority does not change. The default
value is DEFAULT, which is internally mapped to MEDIUM for service
superclasses. If DEFAULT is specified for a service subclass, it inherits
the PREFETCH PRIORITY of its parent superclass.
PREFETCH PRIORITY
cannot be altered for a default subclass (SQLSTATE 5U032).
- OUTBOUND CORRELATOR NONE or OUTBOUND CORRELATOR string-constant
- Specifies whether or not to associate threads from this service
class to an external workload manager service class.
If OUTBOUND
CORRELATOR is set to a string-constant for the
service superclass and OUTBOUND CORRELATOR NONE is set for a service
subclass, the service subclass inherits the OUTBOUND CORRELATOR of
its parent.
- OUTBOUND CORRELATOR NONE
- For a service superclass, specifies that there is no external
workload manager service class association with this service class,
and for a service subclass, specifies that the external workload manager
service class association is the same as its parent.
- OUTBOUND CORRELATOR string-constant
- Specifies the string-constant that is to be
used as a correlator to associate threads from this service class
to an external workload manager service class. The external workload
manager must be active (SQLSTATE 5U030). The external workload manager
should be set up to recognize the value of string-constant.
- BUFFERPOOL PRIORITY DEFAULT | HIGH | MEDIUM | LOW
- This parameter controls the bufferpool priority of pages fetched
by activities in this service class. Valid values are HIGH, MEDIUM,
LOW or DEFAULT (SQLSTATE 42615). Pages fetched by activities in a
service class with higher bufferpool priority are less likely to be
swapped out than pages fetched by activities in a service class with
lower bufferpool priority. If DEFAULT is specified for a service subclass,
it inherits the BUFFERPOOL PRIORITY from its parent superclass.
BUFFERPOOL
PRIORITY cannot be altered for a default subclass (SQLSTATE 5U032).
- COLLECT ACTIVITY DATA
- Specifies that information about each activity that executes in
this service class is to be sent to any active activities event monitor
when the activity completes. The COLLECT ACTIVITY DATA clause is only
valid for a service subclass.
- 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
executes in the service class 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 partition 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 (specified
on the WORK ACTION, SERVICE CLASS, or WORKLOAD), 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 should not be collected for each
activity that executes in this service class.
- COLLECT AGGREGATE ACTIVITY DATA
- Specifies that aggregate activity data should be captured for
this service class and sent to the
statistics event monitor, if one is active. This information
is collected periodically on an interval that is specified by the wlm_collect_int database
configuration parameter. The default is COLLECT AGGREGATE ACTIVITY
DATA BASE. The COLLECT AGGREGATE ACTIVITY DATA clause is only valid
for a service subclass.
- BASE
- Specifies that basic aggregate activity data should be captured
for this service class and sent to the
statistics event monitor, if one is active. Basic aggregate
activity data includes:
- EXTENDED
- Specifies that all aggregate activity data should be captured
for this service class and sent to the
statistics event monitor, if one is active. This includes
all basic aggregate activity data plus:
- Activity data manipulation language (DML) estimated cost histogram
- Activity DML inter-arrival time histogram
- NONE
- Specifies that no aggregate activity data should be captured for
this service class.
- COLLECT AGGREGATE REQUEST DATA
- Specifies that aggregate request data should be captured for this
service class and sent to the
statistics event monitor, if one is active. This information
is collected periodically on an interval specified by the wlm_collect_int database
configuration parameter.
The default is COLLECT AGGREGATE REQUEST DATA NONE. The COLLECT AGGREGATE
REQUEST DATA clause is valid only for a service subclass.
- BASE
- Specifies that basic aggregate request data should be captured
for this service class and sent to the
statistics event monitor, if one is active.
- NONE
- Specifies that no aggregate request data should be captured for
this service class.
- COLLECT AGGREGATE UNIT OF WORK DATA
- Specifies that aggregate unit of work data is to be captured for
this service class and sent to the statistics event monitor, if one
is active. This information is collected periodically on an interval
that is specified by the wlm_collect_int database
configuration parameter. The default, 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 is to be captured
for this service class and 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 service class.
- COLLECT REQUEST METRICS
- Specifies that monitor metrics should be collected for any request
submitted by a connection that is associated with the specified service
superclass and sent to the statistics and unit of work event monitors,
if active. The default is COLLECT REQUEST METRICS NONE. The COLLECT
REQUEST METRICS clause is only valid for a service superclass (SQLSTATE
50U44).
Note: The effective request metrics collection setting is
the combination of the attribute specified by the COLLECT REQUEST
METRICS clause on the service superclass associated with the connection
submitting the request, and the mon_req_metrics database
configuration parameter. If either the service superclass attribute
or the configuration parameter has a value other than NONE, metrics
will be collected for the request.
- BASE
- Specifies that basic metrics will be collected for any request
submitted by a connection associated with the service superclass.
- EXTENDED
- Specifies that basic metrics will be collected for any request
submitted by a connection associated with the service superclass.
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
- NONE
- Specifies that no metrics will be collected for any request submitted
by a connection associated with the service superclass.
- 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 service class during a specific interval. This time includes
both time queued and time executing. This information is only collected
when the COLLECT AGGREGATE ACTIVITY DATA clause is specified, with
either the BASE or EXTENDED option. This clause is only valid for
a service subclass.
- 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 service class are queued during a specific interval.
This information is only collected when the COLLECT AGGREGATE ACTIVITY
DATA clause is specified, with either the BASE or EXTENDED option.
This clause is only valid for a service subclass.
- 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 service class 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 only collected when the COLLECT AGGREGATE ACTIVITY
DATA clause is specified, with either the BASE or EXTENDED option.
This clause is only valid for a service subclass.
- REQUEST 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 requests
running in the service class are executing during a specific interval.
This time does not include the time spent queued. Request execution time is collected in this
histogram on each member where
the request executes. This information is only collected when
the COLLECT AGGREGATE REQUEST DATA clause is specified with the BASE
option. This clause is only valid for a service subclass.
- 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 service class. This information is only collected when
the COLLECT AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED
option. This clause is only valid for a service subclass.
- ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE template-name
- Specifies the template that describes the histogram
used to collect statistical data about the length of time, in milliseconds,
between the arrival of one DML activity and the arrival of the next
DML activity. This information is only collected when the COLLECT
AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED option.
This clause is only valid for a service subclass.
- 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 service class 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.
- ENABLE or DISABLE
- Specifies whether or not connections and activities can be mapped
to the service class.
- ENABLE
- Connections and activities can be mapped to the service class.
- DISABLE
- Connections and activities cannot be mapped to the service class.
New connections or activities that are mapped to a disabled service
class will be rejected (SQLSTATE 5U028). When a service superclass
is disabled, its service subclasses are also disabled. When the service
superclass is re-enabled, its service subclasses return to states
that are defined in the system catalog. A default service class cannot
be disabled (SQLSTATE 5U032).
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
- Only one uncommitted WLM-exclusive SQL
statement at a time is allowed across all members.
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.
- Changes are written to the system catalog, but do not take effect
until after a COMMIT statement, even for the connection that issues
the statement.
- After the ALTER SERVICE CLASS statement
is committed, changes to PREFETCH PRIORITY, OUTBOUND CORRELATOR, and
COLLECT take effect for the next new activity in the service class.
Existing activities in the service class continue to complete their
work using the old settings.
- 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.
- o DATABASE PARTITION can be specified in place of MEMBER, except
when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- o DATABASE PARTITIONS can be specified in place of MEMBERS, except
when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
DB2 10.5 for Linux, UNIX, and Windows