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
>>-CREATE SERVICE CLASS--service-class-name--------------------->
>--+--------------------------------+--------------------------->
'-UNDER--service-superclass-name-'
.-HARD CPU SHARES 1000-------------------.
>--+----------------------------------------+------------------->
| .-HARD-. |
'-+------+--CPU SHARES--integer-constant-'
'-SOFT-'
>--+---------------------------------+-------------------------->
'-CPU LIMIT--+-integer-constant-+-'
'-NONE-------------'
.-PREFETCH PRIORITY DEFAULT-----.
>--+-------------------------------+---------------------------->
'-PREFETCH PRIORITY--+-HIGH---+-'
+-MEDIUM-+
'-LOW----'
.-OUTBOUND CORRELATOR NONE-------------.
>--+--------------------------------------+--------------------->
'-OUTBOUND CORRELATOR--string-constant-'
.-BUFFERPOOL PRIORITY DEFAULT-----.
>--+---------------------------------+-------------------------->
'-BUFFERPOOL PRIORITY--+-HIGH---+-'
+-MEDIUM-+
'-LOW----'
(1) .-COLLECT ACTIVITY DATA--NONE-----------------------------.
>--------+---------------------------------------------------------+-->
'-COLLECT ACTIVITY DATA--| collect-activity-data-clause |-'
.-COLLECT AGGREGATE ACTIVITY DATA NONE----------.
>--+-----------------------------------------------+------------>
| .-BASE-----. |
'-COLLECT AGGREGATE ACTIVITY DATA--+----------+-'
'-EXTENDED-'
.-COLLECT AGGREGATE REQUEST DATA NONE------.
>--+------------------------------------------+----------------->
| .-BASE-. |
'-COLLECT AGGREGATE REQUEST DATA--+------+-'
.-COLLECT AGGREGATE UNIT OF WORK DATA--NONE-----.
>--+-----------------------------------------------+------------>
| .-BASE-. |
'-COLLECT AGGREGATE UNIT OF WORK DATA--+------+-'
.-COLLECT REQUEST METRICS--NONE---------------.
>--+---------------------------------------------+-------------->
| (2) .-BASE-----. |
'-------COLLECT REQUEST METRICS--+----------+-'
'-EXTENDED-'
(3) .-ENABLE--.
>--------| histogram-template-clause |--+---------+------------><
'-DISABLE-'
collect-activity-data-clause
.-MEMBER-.
.-ON COORDINATOR--+--------+-.
|--+----------------------------+------------------------------->
| .-MEMBERS-. |
'-ON ALL--+---------+--------'
.-WITHOUT DETAILS-------------------------------------------------.
>--+-----------------------------------------------------------------+--|
| .-,-------------------------------------. |
| V (4) | |
'-WITH----+-DETAILS---------------------------+-+--+------------+-'
'-SECTION--+----------------------+-' '-AND VALUES-'
'-INCLUDE ACTUALS BASE-'
histogram-template-clause
.-ACTIVITY LIFETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
|--●--+----------------------------------------------------------+-->
'-ACTIVITY LIFETIME HISTOGRAM TEMPLATE--template-name------'
.-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+-----------------------------------------------------------+-->
'-ACTIVITY QUEUETIME HISTOGRAM TEMPLATE--template-name------'
.-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+-------------------------------------------------------------+-->
'-ACTIVITY EXECUTETIME HISTOGRAM TEMPLATE--template-name------'
.-REQUEST EXECUTETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+------------------------------------------------------------+-->
'-REQUEST EXECUTETIME HISTOGRAM TEMPLATE--template-name------'
.-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+---------------------------------------------------------------+-->
'-ACTIVITY ESTIMATEDCOST HISTOGRAM TEMPLATE--template-name------'
.-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+------------------------------------------------------------------+-->
'-ACTIVITY INTERARRIVALTIME HISTOGRAM TEMPLATE--template-name------'
.-UOW LIFETIME HISTOGRAM TEMPLATE SYSDEFAULTHISTOGRAM-.
>--●--+-----------------------------------------------------+--●--|
'-UOW LIFETIME HISTOGRAM TEMPLATE--template-name------'
Notes:
- All COLLECT clauses except for COLLECT
REQUEST METRICS are valid only for a service subclass.
- The COLLECT REQUEST METRICS clause is valid only for a service
superclass.
- The 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-name
- Names the service class. This is a one-part name. It is an
SQL identifier (either ordinary or delimited). 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 identify a service subclass that already exists under the service
superclass (SQLSTATE 42710). If the service class is a service subclass,
the service-class-name must not be the same as
its 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).
- HARD CPU SHARES 1000 or HARD CPU SHARES integer-constant or SOFT 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. The default is HARD CPU SHARES
1000, which allocates 1000 hard shares
to this service class. 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. The default is OUTBOUND CORRELATOR NONE.
- 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. 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.
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 default is COLLECT ACTIVITY DATA
NONE. The COLLECT ACTIVITY DATA clause is valid only for a service
subclass.
- NONE
- Specifies that activity data should not be collected for each
activity that executes in this service class.
- 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. The
default when COLLECT AGGREGATE ACTIVITY DATA is not specified is COLLECT
AGGREGATE ACTIVITY DATA NONE. The default when COLLECT AGGREGATE ACTIVITY
DATA is specified is COLLECT AGGREGATE ACTIVITY DATA BASE.
The COLLECT AGGREGATE ACTIVITY DATA clause is valid only 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
specified by the wlm_collect_int database configuration
parameter. The default is COLLECT AGGREGATE UNIT OF WORK DATA NONE.
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.
- 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 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.
- 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. The HISTOGRAM
TEMPLATE clause is valid only for a service subclass.
- 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. The default is ENABLE.
- 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
- 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