If you want to change a service class definition, use the
ALTER SERVICE CLASS statement.
Before you begin
To alter a service class, you require
SQLADM, WLMADM, or DBADM authority. To specify any clause other than
a COLLECT clause, the authorization ID must include WLMADM or DBADM
authority.
See Workload management DDL statements for more information about prerequisites.
About this task
Activities that have already acquired resources and are running
are usually not affected by the ALTER statement. These activities will hold
their resources and run until completion. (Note that changes to the CPU
shares or CPU limits of a service class that are made through the
ALTER SERVICE CLASS statement take effect immediately, even in the
case of activities that are already running.) However, if a subagent
request is sent to a remote member during the ALTER SERVICE
CLASS operation, the service class definition seen by the coordinator
agent and the subagent can differ. Consider the following example
in which the prefetch priority for the service class is initially
set to MEDIUM:
Table 1. Differences between the views of a coordinator
agent and subagent of an altered service classEvent order |
Connection 1 |
Connection 2 |
1 |
Coordinating agent sends a request to remote
member (prefetch priority of service class was previously set to
MEDIUM) |
|
2 |
|
ALTER SERVICE CLASS issued; set prefetch priority
to HIGH |
3 |
|
COMMIT is issued (the altered service class
property is committed at the catalog member and loaded to memory
at all database members) |
4 |
Remote subagent receives the request. At this
time, the subagent sets the new prefetch priority of HIGH for the
service class definition |
|
This situation described in the previous table is temporary,
and only affects connections that issue subagent requests during the
ALTER SERVICE CLASS operation. All new connections will see the updated
service class definition with the prefetch priority of HIGH.
Procedure
To alter a service class:
- Specify one or more of the following properties for the
service class on the ALTER SERVICE CLASS statement:
- Specify whether the service class is enabled or disabled. If
you change a service class from enabled to disabled, existing connections
or activities remain with the service class and continue to use previously
allocated resources until complete. You can disable a service class
if the work coming to the service class is overwhelming the system,
or you want to reject all work coming to the service class.
When
a service superclass is disabled the following happens:
- The service superclass is disabled.
- Its service subclasses are disabled.
The service subclasses are only disabled while their service
superclass is disabled. When the service superclass is enabled, the
service subclasses return to their previous states as defined in the
catalog table.
When a service subclass is disabled, its service
superclass is not affected, nor other service subclasses associated
with the service superclass.
You cannot explicitly disable a
default service subclass. To prevent new requests from running under
a default service subclass, you must disable the associated service
superclass.
- Specify the agent priority for the service class. When the
agent priority is set to DEFAULT, agents in the service class are
assigned the same priority that the operating system assigns all DB2® threads. If you set the AGENT
PRIORITY parameter to a value other than DEFAULT, the agent threads
are set to a priority equal to the default priority, plus the value
set when the next activity begins. For example, if the default priority
is 20 and you set agent priority to -10, the resulting agent priority
is set to 20 + (-10) = 10.
In the SYSCAT.SERVICECLASSES catalog
view, an agent priority of DEFAULT is represented as -32768.
On Linux and UNIX, the valid values are -20 to 20 (a negative
value indicates a higher relative priority). On Windows operating systems, the valid values
are -6 to 6 (a negative value indicates a lower relative priority)
- Specify the prefetch priority. You can specify the priority
with which agents in the service class can submit their prefetch requests.
Depending on the value specified, the prefetch requests are routed
to the high, medium, or low priority prefetch queues. The default
prefetch priority is medium. If the prefetch priority is altered after
a prefetch request is submitted, the request will not change its priority.
- Specify the buffer pool priority for the
service class which affects how likely pages fetched by activities
in the service class are to be swapped out. For service superclasses,
the DEFAULT value internally maps to LOW. Service subclasses set to
DEFAULT inherit the buffer pool priority from their parent superclasses.
- Specify the outbound correlator string if
you want to associate the DB2 service
class with an AIX® class or a Linux class. A null value indicates
no operating system workload manager association.
If the outbound
correlator is changed from a non-null value to a null value, all threads
in the DB2 service class will disassociate
with the operating system workload manager when the next activity begins.
If
the outbound correlator is set to NONE for a service subclass and
the outbound correlator is specified for the associated service superclass,
the service subclass inherits the outbound correlator specified for
its service superclass.
If a service superclass uses an outbound
correlator, the agent priority of the service superclass must be set
to default.
If a service subclass uses an outbound correlator
(either explicitly as part of the service subclass definition or implicitly
through inheritance from the service superclass), the agent priority
of the service subclass must be set to default.
- Specify
the adding or dropping of a system temporary table space to or from
the list of preferred temporary table spaces for the specified service
subclass.
- Specify the activity data to collect. When activity data collection
is enabled, information about an activity is sent from the coordinator
member to the applicable event monitor at the end of the activity.
You can write data to the event monitor that includes information
about the statement that was run, its compilation environment, and
any applicable input data values. You can also specify that no activity
data is collected. By default, no activity data is collected.
- Collected aggregate activity information. The aggregate activity
information used for the service class only changes after the ALTER
SERVICE CLASS operation is committed.
- The monitoring request metrics collection
level for requests submitted by connections mapped to a subclass under
the specified service superclass. Note that the effective collection
setting for requests running under a service superclass is the combination
of both the service class collection level and the mon_req_metrics database
configuration parameter.
- Whether to alter the histogram templates used by a service
subclass that has enabled aggregate activity data collection using
COLLECT AGGREGATE ACTIVITY DATA or aggregate request data collection
using COLLECT AGGREGATE REQUEST DATA. Updating the histogram templates
used by a service subclass will update the corresponding rows in the
SYSCAT.HISTOGRAMTEMPLATEUSE view which displays the histogram templates
referenced by a service class or work action. For more information
about histograms and histogram templates, see Histograms in workload management.
- Commit your changes. When you commit your
changes the service class is updated in the SYSCAT.SERVICECLASSES
view.