CREATE SERVICE CLASS statement

The CREATE SERVICE CLASS statement defines 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 WLMADM or DBADM authority.

Syntax

collect-activity-clause

collect-activity-data-clause

histogram-template-clause

Notes:

¹ The FOR WORKLOAD TYPE clause is valid only for a service superclass (SQLSTATE 5U044).
² The FOR WORKLOAD TYPE clause is valid only for a service superclass (SQLSTATE 5U044).
³ The ADMISSION QUEUE ORDER clause is valid only for a service subclass (SQLSTATE 5U043).
⁴ The ADMISSION QUEUE ORDER clause is valid only for a service subclass (SQLSTATE 5U043).
⁵ The DEGREE SCALEBACK DEFAULT option is valid only for a service subclass (SQLSTATE 5U043).
⁶ The MAXIMUM DEGREE DEFAULT option is valid only for a service subclass (SQLSTATE 5U043).
⁷ The COLLECT AGGREGATE REQUEST DATA clause is valid only for service subclasses.
⁸ The COLLECT AGGREGATE UNIT OF WORK DATA clause is valid only for service subclasses.
⁹ The COLLECT REQUEST METRICS clause is valid only for a service superclass.
¹⁰ The REQUEST EXECUTETIME AND UOW LIFETIME HISTOGRAM TEMPLATE clauses are valid only for a service subclass.
¹¹ The DETAILS keyword is the minimum to be specified, followed by the option separated by a comma.

Description

service-class-nameName of the service class to be created. This is a one-part name that is also an SQL identifier (either ordinary or delimited). The following restrictions apply:

If the service class is a service superclass, the service class name must not identify a service superclass that already exists in the catalog (SQLSTATE 42710).
If the service class is a service subclass, the service class name must not be the same as its service superclass, and must not identify a service subclass that already exists under the service superclass (SQLSTATE 42710).
The name must not begin with the characters 'SYS' (SQLSTATE 42939).

UNDER service-superclass-name

Specifies that the service class is a subclass of service superclass service-superclass-name. If UNDER is not specified, the service class is a service superclass. The service-superclass-name must identify a service superclass that exists for the database (SQLSTATE 42704). The service superclass cannot be a default service class (SQLSTATE 5U029).

FOR WORKLOAD TYPE

Specifies the type of workload that is expected to run in the service superclass. This dictates how the service superclass is configured.

CUSTOM: The service superclass attributes are set to their default values. This is the default.
BATCH: The service superclass is configured to optimize it for large, batch-oriented activities.
INTERACTIVE: The service superclass is configured to optimize its response time for short activities.
MIXED: The service superclass is configured to handle a mixed set of activities of varying types and complexity.

RESOURCE SHARES

Specifies the number of shares of resources to which this service class is entitled, and whether the service class is allowed to exceed this number when other service classes in the same scope are not using their full entitlements. This value affects the amount of work the workload manager (WLM) adaptive admission control allows into the system.

HARD: The service class is not allowed to exceed its resource share entitlement.
SOFT: The service class is allowed to exceed its resource share entitlement when other service classes are not using their full entitlements.

Valid values are integers 1 - 65535. The default is SOFT RESOURCE SHARES 1000.

Note: To use resource shares with WLM, you must enable the wlm_admission_ctrl configuration parameter.

CPU SHARES

Specifies the number of CPU shares that the workload manager (WLM) dispatcher allocates to this service class when work is executing within this service class, and whether the service class is allowed to exceed this number when other service classes in the same scope are not using their full entitlement.

HARD: The service class is not allowed to exceed its CPU share entitlement.
SOFT: The service class is allowed to exceed its CPU share entitlement when other service classes are not using their full entitlements.

Valid values are integers 1 - 65535. The default is HARD CPU SHARES 1000.

Note: To use CPU shares with WLM dispatcher, you must enable the wlm_disp_cpu_shares database manager configuration parameter.

CPU LIMIT

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.

ACTIVITY SORTMEM LIMIT

Specifies the maximum percentage of the configured shared sort memory (SHEAPTHRES_SHR) that individual queries executing in the service class are allowed to consume. Queries requiring more memory than the configured limit will have their individual per-operator SORTHEAP values reduced at runtime and memory requests will be throttled if they exceed the limit. Valid values for the integer-constant are integers between 10 and 100. You can also specify NONE to indicate there is no activity sort memory limit. The default is NONE.

The effective sort memory limit for a query will be the most restrictive of the limit defined at the subclass, superclass and database via the ACT_SORTMEM_LIMIT database configuration parameter. The sort memory limit applied to an activity is determined when the activity is first admitted for execution. The applied sort memory limit will not change if a query is remapped at runtime to a different service subclass.

The activity sort memory limit will only be enforced for queries that are managed by the adaptive workload manager. If the adaptive workload manager is disabled (WLM_ADMISSION_CTRL database config parameter is set to NO) or a query bypasses the adaptive workload manager, no sort memory limit will be applied to the query regardless of the service class it runs in.

Note: Setting an activity sort memory limit too low may result in reduced performance for queries.

MINIMUM RESOURCE SHARE integer-constant PERCENT

Specifies the percentage of entitled resources used by WLM adaptive admission control that is to be held in reserve for the service class when other service classes exceed their admission resource entitlement. Valid values for integer-constant are integers 0 - 100. The default is 0.

ADMISSION QUEUE ORDER

Specifies the queue order for activities queued by WLM adaptive admission control.

FIFO: Requests are queued in a first-in first-out order. This is the default.
LATENCY: The position of a request in the queue is based on its estimated execution time (that is, its latency) relative to the amount of time that has elapsed since it joined the queue.

DEGREE SCALEBACK

Specifies whether work running in this service class may have its degree scaled back. Queries set to DEGREE ANY may have their actual runtime degree scaled back by the database manager based on current CPU loads.

Scaling back the degree for service classes running simple queries may result in less contention and improved throughput. Disabling degree scale back for service classes with complex queries can help ensure more consistent and predictable response times. A setting of DEFAULT means a service subclass inherits its DEGREE SCALEBACK setting from the parent superclass. The DEFAULT setting is only applicable to service subclasses. The default setting for a service superclass is ON. The default value for a service subclass is DEFAULT.

MAXIMUM DEGREE

Specifies the maximum runtime degree of parallelism for activities that are running in this service class.

DEFAULT: This service subclass inherits its maximum degree value from its parent superclass. This value is the default for a service subclass. This setting is applicable only to service subclasses.
NONE: This service class does not specify a maximum runtime degree for assigned applications. The actual runtime degree is determined as the lower of the value of max_querydegree configuration parameter, the value set by SET RUNTIME DEGREE command, the SQL statement compilation degree and the MAXIMUM DEGREE value set on the Workload. This is the default for a service superclass.; Specifies the maximum degree of parallelism for this service class. Valid values are 1 to 32767. The actual runtime degree is determined as the lower of this degree, the value of max_querydegree configuration parameter, the value set by SET RUNTIME DEGREE command, the SQL statement compilation degree and the MAXIMUM DEGREE set on the Workload.

PREFETCH PRIORITY

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. 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 the 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.

The prefetch priority cannot be altered for a default subclass (SQLSTATE 5U032).

OUTBOUND CORRELATOR

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. This is the default.
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 the specified string constant.

BUFFERPOOL PRIORITY

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. The default value is DEFAULT, which is internally mapped to LOW for service superclasses. If DEFAULT is specified for a service subclass, it inherits the bufferpool priority from its parent superclass.

The 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.

NONE

Specifies that activity data should not be collected for each activity that executes in this service class. This is the default.

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 member 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, 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 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.

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.

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

This is the default when COLLECT AGGREGATE ACTIVITY DATA is specified, but without a value.

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. This is the default when COLLECT AGGREGATE ACTIVITY DATA is not specified.

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 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. This is the default.

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 specified by the wlm_collect_int database configuration parameter. The COLLECT AGGREGATE UNIT OF WORK DATA clause is valid only for a service subclass.

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 includes:

Unit of work lifetime histogram

NONE

Specifies that no aggregate unit of work data is to be collected for this service class. This is the default.

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 COLLECT REQUEST METRICS clause is valid only 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.

NONE

Specifies that no metrics will be collected for any request submitted by a connection associated with the service superclass. This is the default.

BASE

Specifies that basic metrics will be collected for any request submitted by a connection associated with the service superclass.

EXTENDED

Specifies that basic aggregate request data should be captured for this service class and sent to the statistics event monitor, if one is active. 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

histogram-template-clause Specifies the histogram templates to use when collecting aggregate activity data for activities executing in the service class.

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. The default is SYSDEFAULTHISTOGRAM. 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 database activities running in the service class are queued during a specific interval. The default is SYSDEFAULTHISTOGRAM. 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 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. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified, with either the BASE or EXTENDED option. Only activities at nesting level 0 are considered for inclusion in the histogram.
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. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE REQUEST DATA clause is specified with the BASE 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 service class. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED option. Only activities at nesting level 0 are considered for inclusion in the histogram.
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. The default is SYSDEFAULTHISTOGRAM. This information is collected only when the COLLECT AGGREGATE ACTIVITY DATA clause is specified with the EXTENDED option.
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. This is the default.
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

The maximum number of service subclasses that can be created under a service superclass is 61 (SQLSTATE 5U027).
The maximum number of service superclasses that can be created for a database is 64 (SQLSTATE 5U027).
A workload management (WLM)-exclusive SQL statement must be followed by a COMMIT or a ROLLBACK statement (SQLSTATE 5U027). 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

A default subclass, SYSDEFAULTSUBCLASS, is automatically created for every service superclass.
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.
Syntax alternatives: The following are supported for compatibility with previous versions of Db2® and with other database products. These alternatives are non-standard and should not be used.
- DATABASE PARTITION can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- 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: Create a service superclass named PETSALES. The default subclass for PETSALES is automatically created.
```
   CREATE SERVICE CLASS PETSALES
```
Example 2: Create a service subclass named DOGSALES under service superclass PETSALES. Set service class DOGSALES as disabled.
```
   CREATE SERVICE CLASS DOGSALES UNDER PETSALES DISABLE
```
Example 3: Create a service superclass named BARNSALES with a prefetcher priority of LOW. The default subclass for BARNSALES is automatically created. Prefetch requests submitted by agents in the BARNSALES service class will go to the low priority prefetch queue.
```
   CREATE SERVICE CLASS BARNSALES PREFETCH PRIORITY LOW
```