ALTER WORK CLASS SET statement

The ALTER WORK CLASS SET statement adds, alters, or drops work classes within 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

Read syntax diagramSkip visual syntax diagramALTER WORK CLASS SETwork-class-set-name 1ADDwork-class-definitionALTERwork-class-alterationDROPWORK CLASSwork-class-name
work-class-definition
Read syntax diagramSkip visual syntax diagramWORK CLASSwork-class-name work-attributesposition-clause
work-attributes
Read syntax diagramSkip visual syntax diagramWORK TYPEREADfor-from-to-clausedata-tag-clauseWRITEfor-from-to-clausedata-tag-clauseCALLschema-clauseDMLfor-from-to-clausedata-tag-clauseDDLLOADALLfor-from-to-clauseschema-clausedata-tag-clause
for-from-to-clause
Read syntax diagramSkip visual syntax diagram FOR TIMERONCOSTCARDINALITYFROMfrom-valueTOUNBOUNDEDTOto-value
data-tag-clause
Read syntax diagramSkip visual syntax diagram DATA TAG LIST CONTAINS  integer-constant
schema-clause
Read syntax diagramSkip visual syntax diagramROUTINES IN SCHEMAschema-name
position-clause
Read syntax diagramSkip visual syntax diagramPOSITION LASTPOSITION BEFOREwork-class-namePOSITION AFTERwork-class-namePOSITION ATinteger
work-class-alteration
Read syntax diagramSkip visual syntax diagramWORK CLASSwork-class-name 2for-from-to-alter-clauseschema-alter-clausedata-tag-alter-clauseposition-clause
for-from-to-alter-clause
Read syntax diagramSkip visual syntax diagramFOR TIMERONCOSTCARDINALITYFROMfrom-valueTOUNBOUNDEDTOto-valueALL UNITS UNBOUNDED
schema-alter-clause
Read syntax diagramSkip visual syntax diagramROUTINES IN SCHEMAschema-nameALL
data-tag-alter-clause
Read syntax diagramSkip visual syntax diagramDATA TAG LIST CONTAINS integer-constantANY
Notes:
  • 1 The ADD, ALTER, and DROP clauses are processed in the order in which they are specified.
  • 2 The same clause must not be specified more than once.

Description

work-class-set-name
Identifies the work class set that is to be altered. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The work-class-set-name must identify a work class set that exists at the current server (SQLSTATE 42704).
ADD
Adds a work class to the work class set. For details, see CREATE WORK CLASS SET.
ALTER
Alters the database activity attributes and the position of a specific work class within the work class set.
WORK CLASS work-class-name
Identifies the work class to be altered. The work-class-name must identify a work class that exists within the work class set at the current server (SQLSTATE 42704).
DROP
Drops the work class from the work class set.
WORK CLASS work-class-name
Identifies the work class to be dropped. The work-class-name must identify a work class that exists within the work class set at the current server (SQLSTATE 42704). A work class cannot be dropped if there is a work action in any of the work action sets associated with this work class set that is dependent on it (SQLSTATE 42893).
for-to-from-alter-clause
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:
  • ALL
  • DML
  • READ
  • WRITE
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. This range is only used for the following work types:
  • ALL
  • DML
  • READ
  • WRITE
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).
ALL UNITS UNBOUNDED
Indicates that no range is to be specified in the FROM from-value TO to-value clause, and that all work that falls within the specified work type is to be included.
schema-alter-clause
ROUTINES
This clause is only used if the work type is CALL or ALL and the database activity is a CALL statement.
IN SCHEMA schema-name
Specifies the schema name of the procedure that the CALL statement will be calling.
ALL
Specifies that all schemas are included.
data-tag-alter-clause
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.
DATA TAG LIST CONTAINS ANY
Indicates that any data tag setting, including no data tag, is valid for the work class. All work that falls within the specified work type is to be included, regardless of the data tag.
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.
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: Alter work class set LARGE_QUERIES and set the two existing work classes to have each range starting at 100 000, keeping the range unbounded. Add a third work class for all SELECT statements that have an estimated timeron cost greater than or equal to 10 000, and position this work class to take priority over the existing two work classes.
       ALTER WORK CLASS SET LARGE_QUERIES
     ALTER WORK CLASS LARGE_ESTIMATED_COST
      FOR TIMERONCOST FROM 100000 TO UNBOUNDED
     ALTER WORK CLASS LARGE_CARDINALITY
      FOR CARDINALITY FROM 100000 TO UNBOUNDED
     ADD WORK CLASS LARGE_SELECTS WORK TYPE READ
      FOR TIMERONCOST FROM 10000 TO UNBOUNDED POSITION AT 1
  • Example 2: Alter a work class set named DML_STATEMENTS to add a work class that represents all DML SELECT statements that contain a DELETE, INSERT, MERGE, or UPDATE statement.
       ALTER WORK CLASS SET DML_STATEMENTS
     ADD WORK CLASS UPDATE_CLASS WORK TYPE WRITE