The CREATE WORK CLASS SET statement defines a work class
set.
Invocation
This statement can be embedded
in an application program or issued interactively. 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 WORK CLASS SET--work-class-set-name------------------->
>--+-------------------------------------+---------------------><
| .-,-------------------------. |
| V | |
'-(----| work-class-definition |-+--)-'
work-class-definition
.-WORK CLASS-.
|--+------------+--work-class-name------------------------------>
>--| work-attributes |--| position-clause |---------------------|
work-attributes
|--WORK TYPE---------------------------------------------------->
>--+-READ--+------------------------+--+---------------------+-----------------------+--|
| '-| for-from-to-clause |-' '-| data-tag-clause |-' |
+-WRITE--+------------------------+--+---------------------+----------------------+
| '-| for-from-to-clause |-' '-| data-tag-clause |-' |
+-CALL--+-------------------+-----------------------------------------------------+
| '-| schema-clause |-' |
+-DML--+------------------------+--+---------------------+------------------------+
| '-| for-from-to-clause |-' '-| data-tag-clause |-' |
+-DDL-----------------------------------------------------------------------------+
+-LOAD----------------------------------------------------------------------------+
'-ALL--+------------------------+--+-------------------+--+---------------------+-'
'-| for-from-to-clause |-' '-| schema-clause |-' '-| data-tag-clause |-'
for-from-to-clause
.-TO--UNBOUNDED-.
|--FOR--+-TIMERONCOST-+--FROM--from-value--+---------------+----|
'-CARDINALITY-' '-TO--to-value--'
data-tag-clause
|--DATA TAG LIST CONTAINSinteger-constant-----------------------|
schema-clause
|--ROUTINES IN SCHEMA--schema-name------------------------------|
position-clause
.-POSITION LAST--------------------.
|--+----------------------------------+-------------------------|
+-POSITION BEFORE--work-class-name-+
+-POSITION AFTER--work-class-name--+
'-POSITION AT--position------------'
Description
- work-class-set-name
- Names the work class set. This is a one-part name. It is an
SQL identifier (either ordinary or delimited). The work-class-set-name must
not identify a work class set that already exists at the current server
(SQLSTATE 42710). The name must not begin with the characters 'SYS'
(SQLSTATE 42939).
- work-class-definition
- Specifies the definition of the work class.
- WORK CLASS work-class-name
- Names the work class. The work-class-name must
not identify a work class that already exists within the work class
set at the current server (SQLSTATE 42710). The work-class-name cannot
begin with 'SYS' (SQLSTATE 42939).
- work-attributes
- The attributes of the database activity must match all of the
attributes specified in this work class if that activity is to be
associated with this work class.
- WORK TYPE
- Specifies the type of database activity.
- READ
- This activity includes the following statements:
- All SELECT or SELECT INTO statements that do not contain a DELETE,
INSERT, MERGE, or UPDATE statement, and all VALUES INTO
statements
- All XQuery statements
- WRITE
- This activity includes the following statements:
- UPDATE
- DELETE
- INSERT
- MERGE
- All SELECT statements that contain a DELETE, INSERT, or UPDATE
statement, and all VALUES INTO statements
- CALL
- Includes the CALL statement. A CALL statement is considered for
a work class with a work type of CALL or ALL.
- DML
- Includes the statements listed under READ and WRITE.
- DDL
- This activity includes the following statements:
- ALTER
- CREATE
- COMMENT
- DECLARE GLOBAL TEMPORARY TABLE
- DROP
- FLUSH PACKAGE CACHE
- GRANT
- REFRESH TABLE
- RENAME
- REVOKE
- SET INTEGRITY
- LOAD
- DB2® load
operations.
- ALL
- All recognized workload management (WLM) activity that falls under
any one of the keywords previously listed within the description for WORK
TYPE.
- FOR
- Indicates the type of information that is being specified in the
FROM from-value TO to-value clause.
The FOR clause is only used for the following work types:
- TIMERONCOST
- The estimated cost of the work, in timerons. This value is used
to determine whether the work falls within the range specified in
the FROM from-value TO to-value clause.
- CARDINALITY
- The estimated cardinality of the work. This value is used to determine
whether the work falls within the range specified in the FROM from-value TO to-value clause.
- FROM from-value TO UNBOUNDED or FROM from-value TO to-value
- Specifies the range of either timeron value (for estimated cost)
or cardinality within which the database activity must fall if it
is to be part of this work class. The range is inclusive of from-value and to-value.
If this clause is not specified for the work class, all work that
falls within the specified work type will be included (that is, the
default is FROM 0 TO UNBOUNDED). This range is only used
for the following work types:
- FROM from-value TO UNBOUNDED
- The from-value must be zero or a positive DOUBLE
value (SQLSTATE 5U019). The range has no upper bound.
- FROM from-value TO to-value
- The from-value must be zero or a positive DOUBLE
value and the to-value must be a positive DOUBLE
value. The from-value must be smaller than or equal
to the to-value (SQLSTATE 5U019).
- DATA TAG LIST CONTAINS integer-constant
- Specifies the
value of the tag given to any data which the database activity might
touch if it is to be part of this work class. If the clause is not
specified for the work class, all work that falls within the specified
work type, regardless of what data it might touch, will be included
(that is, the default is to ignore the data tag). This clause is used
only if the work type is READ, WRITE, DML, or ALL and the database
activity is a DML statement. Valid
values for integer-constant are integers from 1
to 9.
- schema-clause
- ROUTINES IN SCHEMA schema-name
- Specifies the schema name of the procedure that the CALL statement
will be calling. This clause is only used if the work type is CALL
or ALL and the database activity is a CALL statement. If no value
is specified, all schemas are included.
- position-clause
- POSITION
- Specifies where this work class is to be placed within the work
class set, which determines the order in which work classes are evaluated. When
performing work class assignment at run time, the database manager
first determines the work class set that is associated with the object,
either the database or a service superclass. The first matching work
class within that work class set is then selected. If this keyword
is not specified, the work class is placed in the last position.
- LAST
- Specifies that the work class is to be placed last in the ordered
list of work classes within the work class set. This is the default.
- BEFORE work-class-name
- Specifies that the work class is to be placed before work class work-class-name in
the list. The work-class-name must identify a work
class in the work class set that exists at the current server (SQLSTATE
42704).
- AFTER work-class-name
- Specifies that the work class is to be placed after work class work-class-name in
the list. The work-class-name must identify a work
class in the work class set that exists at the current server (SQLSTATE
42704).
- AT position
- Specifies the absolute
position at which the work class is to be placed within the work class
set in the ordered list of work classes. This value can be any positive
integer (not zero) (SQLSTATE 42615). If position is
greater than the number of existing work classes plus one, the work
class is placed at the last position within the work class set.
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
- Changes are written to the system catalog, but do not take effect until
they are committed, even for the connection that issues the statement.
- Only one uncommitted
WLM-exclusive SQL statement at a time is allowed across all partitions.
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.
Examples
- Example 1: Create a work class set named LARGE_QUERIES
that has a set of work classes representing all DML with an estimated
cost greater than 9999 and an estimated cardinality greater than 1000.
CREATE WORK CLASS SET LARGE_QUERIES
(WORK CLASS LARGE_ESTIMATED_COST WORK TYPE DML
FOR TIMERONCOST FROM 9999 TO UNBOUNDED,
WORK CLASS LARGE_CARDINALITY WORK TYPE DML
FOR CARDINALITY FROM 1000 TO UNBOUNDED)
- Example 2: Create a work class set named DML_SELECTS that
has a work class representing all DML SELECT statements that do not
contain a DELETE, INSERT, MERGE, or UPDATE statement.
CREATE WORK CLASS SET DML_SELECTS
(WORK CLASS SELECT_CLASS WORK TYPE READ)