DB2 Version 10.1 for Linux, UNIX, and Windows

ALTER SERVICE CLASS statement

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-------------'                                         |     
         +-AGENT PRIORITY--+-DEFAULT----------+------------------------------------+     
         |                 '-integer-constant-'                                    |     
         +-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 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 DB2® workload manager 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.

AGENT PRIORITY DEFAULT or AGENT PRIORITY integer-constant

Specifies the relative (delta) operating system priority of agents running in the service class or the normal priority of threads running in DB2. The default value is DEFAULT.

Important: The agentpri database manager configuration is deprecated since Version 9.5. It can still be used in pre-Version 9.5 data servers and clients. Also, agent priority for the WLM service class has been deprecated in Version 10.1 and might be removed in a future release. Start to use the WLM dispatcher capability instead of agent priority. For more information, see Agent priority of service classes has been deprecated.

When set to DEFAULT, no special action is taken, and agents in the service class are scheduled according to the normal priority that the operating system schedules all DB2 threads. When this parameter is set to a value other than DEFAULT, agents are set to a priority that is equal to the normal priority plus AGENT PRIORITY when the next activity begins. For example, if the normal priority is 20 and AGENT PRIORITY is set to -10, the priority of agents in the service class is set to 20 - 10 = 10.

Note: Agent priority and WLM dispatcher shares cannot be used together. When the dispatcher is enabled by setting the value of the wlm_dispatcher database manager configuration parameter to ON, the specified agent priority setting is ignored and agent priority is set to the default value until the dispatcher is disabled.

DB2 workload manager (WLM) does not assign service class agent priority to work being done within a fenced mode process (FMP). Fenced procedures do not run their logic within a service class. These fenced procedures run within the DB2 FMP and this work is not done by DB2 agents. As a reminder, DB2 WLM controls DB2 agents.

On UNIX operating systems and Linux, valid values are DEFAULT and -20 to 20 (SQLSTATE 42615). Negative values denote a higher relative priority. Positive values denote a lower relative priority.

On Windows operating systems, valid values are DEFAULT and -6 to 6 (SQLSTATE 42615). Negative values denote a lower relative priority. Positive values denote a higher relative priority.

If AGENT PRIORITY is DEFAULT for a service subclass, it inherits the AGENT PRIORITY value of its parent superclass. AGENT PRIORITY cannot be altered for a default subclass (SQLSTATE 5U032). AGENT PRIORITY must be set to DEFAULT if OUTBOUND CORRELATOR is set (SQLSTATE 42613).

Note: On AIX®, the instance owner must have CAP_NUMA_ATTACH and CAP_PROPAGATE capabilities to set a higher relative priority for agents in a service class using AGENT PRIORITY. To grant these capabilities, logon as root and run the following command:

chuser capabilities=CAP_NUMA_ATTACH,CAP_PROPAGATE

On Solaris 10 or higher, the instance owner must have the proc_priocntl privilege to set a higher relative priority for agents in a service class using AGENT PRIORITY. To grant this privilege, logon as root and run the following command:

usermod -K defaultpriv=basic,proc_priocntl db2user

In this example, proc_priocntl is added to the default privilege set of user db2user.

Moreover, when DB2 is running in a non-global zone of Solaris, the proc_priocntl privilege must be added to the zone's limit privilege set. To grant this privilege to the zone, logon as root and run the following command:

global# zonecfg -z db2zone
zonecfg:db2zone> set limitpriv="default,proc_priocntl"

In this example, proc_priocntl is added to the limit privilege set of zone db2zone.

On Solaris 9, there is no facility for DB2 to raise the relative priority of agents. Upgrade to Solaris 10 or higher to use the service class agent priority.

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 must be set to NONE if the AGENT PRIORITY is not set to DEFAULT (SQLSTATE 42613).

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.

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:

Estimated activity cost high watermark
Rows returned high watermark
Temporary table space usage high watermark
Note: 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 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 DB2 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 DB2 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 DB2 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 DB2 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 AGENT PRIORITY, 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.

Examples

Example 1: Alter the agent priority of agents in service superclass PETSALES to raise it from DEFAULT to the highest possible value (shown for UNIX and Linux operating systems; on Windows operating systems, substitute 6).
```
   ALTER SERVICE CLASS PETSALES AGENT PRIORITY -20
```
Example 2: Alter service superclass BARNSALES and add an outbound correlator 'osLowPriority'. Threads running in the service superclass and its service subclasses will have the outbound correlator 'osLowPriority' associated with them.
```
   ALTER SERVICE CLASS BARNSALES OUTBOUND CORRELATOR 'osLowPriority'
```