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.
Work type keyword | Applicable SQL statements |
---|---|
READ, including SET statements with embedded READ SQL |
|
WRITE, including SET statements with embedded WRITE SQL |
|
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 |
|
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. |
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
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: