Work identification by type, cost, or data accessed with work classes

In addition to using connection attributes that focus on the origin of activities with workloads, you can identify activities based on the type, cost or data that may be accessed through the creation of a work class set containing a work class.

A work class is a method of categorizing individual database activities based on attributes of the activities. If a work class has a work action defined for it, the work action will be applied to the work class and determines how the activities in the work class are managed. For more information, see Apply controls to types of activities with work action sets.

The following table shows the type keywords available for work classes and the SQL statements that correspond to the different keywords. Except for the load utility, all the statements in the following table are intercepted immediately before execution in the processing of an EXECUTE, EXECUTE IMMEDIATE, or OPEN request. The load utility, when issued from a client, might issue requests before starting the actual load operation on the data server.

Table 1. Work types
Work type keyword	Applicable SQL statements
READ, including SET statements with embedded READ SQL	All SELECT statements (select into, values into, full select) Exception: SELECT statements containing a DELETE, INSERT, or UPDATE are not included. All XQuery statements
WRITE, including SET statements with embedded WRITE SQL	All UPDATE statements (searched, positioned) All DELETE statements (searched, positioned) All INSERT statements (values, subselect) All MERGE statements All SELECT statements containing a DELETE, INSERT, or UPDATE statement
CALL	CALL statement The CALL statement is only classified under the CALL and ALL work class types. Note: Both anonymous blocks and autonomous routines are classified as CALL statements.
DML, including SET statements with embedded READ or WRITE SQL	All statements that are classified under the READ and WRITE work class types.
DDL	All ALTER statements All CREATE statements COMMENT statement DECLARE GLOBAL TEMPORARY TABLE statement DROP statement FLUSH PACKAGE CACHE statement All GRANT statements REFRESH TABLE All RENAME statements All REVOKE statements SET INTEGRITY statement
LOAD	Load utility The load utility is only classified under the LOAD and ALL work class types.
ALL	Work types that are represented by all of the preceding keywords. Note: If the action is a threshold, the database activity that the threshold is applied to depends on the type of threshold. For example, if the threshold type is ESTIMATEDSQLCOST, only DML activity with an estimated cost (in timerons) is affected by the threshold. For more information, see Example: Working with a work class defined with the ALL keyword.

The following figure shows a hierarchical view of the work type keywords:

SQL statements that do not fall under any of the available keywords are not classified, and behave as though no work class and work class set exists. For example, if the statement is SET SCHEMA and the only work class in the work class set has a work type of DML, that statement is not classified and no work action can be applied to it. So, if the action is MAP, the SET SCHEMA activity runs in the default service subclass (SYSDEFAULTSUBCLASS). If the action is a threshold, no threshold is applied to the activity.

Additional identification

Work classes also permit you to use predictive elements in the identification for DML work (or READ and WRITE statements). Predictive elements are useful because they provide information about database activities that can be used to take action before these activities start consuming resources on the data server. The following table provides information about predictive elements supported by work classes:

Table 2. Characteristics for predictive identification
Predictive element	Description
Estimated cost	Uses the estimated cost available from the Db2® compiler to include DML within a given timeron range (for example, create a work class for all large queries with an estimated cost over 1 000 000 timerons)
Estimated cardinality	Uses the estimated rows returned (cardinality) from the Db2 compiler to include DML within a given range of rows returned (for example, create a work class for large queries that are estimated to return more than 500 000 rows)
Estimated data accessed	Uses the estimated data accessed from the Db2 compiler to include DML that accesses data assigned a specific data tag (for example, create a work class for queries that are estimated to access data in table spaces tagged with a data tag value of 3) Note: The set of table spaces that the Db2 compiler predicts will be accessed is based on the optimized SQL statement, which might differ from the user-specified SQL statement. In some cases, for example, when inserting into range partitioned tables, the number of table spaces that are predicted to be accessed is more than the expected table spaces.

You can also identify activities by using the schema name of the procedure that a CALL statement calls.

Based on workload attributes and work class types, you can identify work and prepare it for the next stage, the management of the work.

For more information on working with work classes and work class sets, refer the following topics: