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 class| Event 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 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.
- 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.
DB2 10.5 for Linux, UNIX, and Windows