Creating a work class

To create a work class, use the CREATE WORK CLASS SET statement or the ALTER WORK CLASS SET statement.

To create a work class:

1. Create a work class at the same time you create a new work class set or add the new work class to an existing work class set:
   • To create a new work class that is added to a new work class set, use the WORK CLASS keyword of the CREATE WORK CLASS SET statement.
   • To create a new work class that is added to an existing work class set, use the ADD WORK CLASS keyword of the ALTER WORK CLASS SET statement.
   Specify one or more of the following properties for the new work class:
   • A name for the work class. This name must be unique in the work class set.
   • Attributes for the work class. These attributes are used to associate an activity with the work class:
     • The type of work that the work class is to be used for. Use the WORK TYPE parameter to specify this characteristic.
       • READ, which represents non-updating SELECT activities, and all XQuery activities. When you specify the READ keyword, you can also specify an optional for-from-to-clause or data-tag-clause argument.

         • Use the optional for-from-to-clause argument to specify a range for either the cost of the statement in timerons, or its cardinality (that is, the number of rows returned). You must specify a numeric value for the first value. For the second value, you can specify either a numeric value, or the value UNBOUNDED to indicate that you do not want to impose an upper limit on either the cost or cardinality of the activity. You can also specify this argument for the WRITE keyword, the DML keyword, and the ALL keyword.

           For example, to associate SELECT activities that have a cost of 5000 timerons or more with this work class, you would specify:

           WORK TYPE READ FOR TIMERONCOST FROM 5000 TO UNBOUNDED

         • Use the optional data-tag-clause argument to specify a data tag identifying data that the activity is estimated to access. You can specify a value between 1 and 9. If the data-tag-clause is not specified no restriction is imposed on the type of data that a query accesses. You can also specify this argument for the WRITE keyword, the DML keyword, and the ALL keyword.

           For example, to associate SELECT activities with this work class, that are estimated to access data in table spaces that have been assigned a data tag value of 1, you would specify:

           WORK TYPE READ DATA TAG LIST CONTAINS 1

       • WRITE, which represents SQL activities that update data in the database. When you specify the WRITE keyword, you can also specify an optional for-from-to-clause or data-tag-clause argument.
         For example, to associate all data writing activities with this work class that, at compile time, are estimated to touch data tagged with a value of 5, you would specify:

         WORK TYPE WRITE FOR CARDINALITY FROM 50 TO 100

       • CALL, which represents CALL activities.
         When you specify the CALL keyword, you can also specify the ROUTINES IN SCHEMA keyword to indicate that only CALL activities to routines in a specific schema should be associated with this work class. For example, if you only want to associate calls to routines in the ACCOUNTS schema to this work class, you would specify:

         WORK TYPE CALL ROUTINES IN SCHEMA ACCOUNTS

       • DML, which represents SQL activities covered by both the READ and WRITE keywords.
         For example, to associate all DML activities that have an estimated cost in timerons from 500 to 1000 and, at compile time, are estimated to touch data tagged with a value of 8, you would specify:

         WORK TYPE DML FOR TIMERONCOST FROM 500 TO 1000 DATA TAG LIST CONTAINS 8 

       • DDL, which represents the following activities:
         • ALTER
         • CREATE
         • COMMENT
         • DECLARE GLOBAL TEMPORARY TABLE
         • DROP
         • FLUSH PACKAGE CACHE
         • GRANT
         • REFRESH TABLE
         • RENAME
         • REVOKE
         • SET INTEGRITY
         For example, to associate all DDL activities with this work class, you would specify:

         WORK TYPE DDL

       • LOAD, which represents a LOAD activity.
         For example, to associate LOAD activities to this work class, you would specify:

         WORK TYPE LOAD

       • ALL, which represents all the work types indicated by all the preceding keywords.

         When you specify ALL for a work class type, you can also specify the ROUTINES IN SCHEMA keyword to indicate that only CALL activities to routines in a specific schema should be associated with this work class. You can also specify the for-from-to-clause argument to indicate that all DML activities that have an estimated timeron cost or cardinality specified fall into this class. For example, to associate both DML activities that have a cardinality of 300 to 1500 rows and routines that are called from the NEWHIRES schema to this work class, you would specify the following statement.You can also specify the data-tag-clause argument to indicate all DML activities that access data in table spaces that are tagged with a data tag value. Because this work class has a type of ALL, it would also apply to other activities that do not have a schema or cardinality, such as LOAD activities and DDL activities.

         WORK TYPE ALL FOR CARDINALITY FROM 300 TO 1500 ROUTINES
         IN SCHEMA NEWHIRES

     • Optional. The position of the work class in the work class set. The position of the work class in the work class set determines the order in which the work class is evaluated when classifying an activity to a work class. When work class assignment occurs, the data server first determines the work class set associated with the object (either a service superclass or the database), then selects the first matching work class in the work class set that has a work action associated with it. Use the POSITION keyword to specify one of the following:
       • LAST. The work class is placed at the end of the list of work classes in the work class set. For example:

         WORK TYPE ... POSITION LAST

       • BEFORE work-class-name. The work class is to be created in the work class set and positioned before the specified work class. For example:

         WORK TYPE ... POSITION BEFORE LARGEDDL

       • AFTER work-class-name. The work class is to be created in the work class set and positioned after the specified work class. For example:

         WORK TYPE ... POSITION AFTER LARGEDDL

       • AT integer. The work class is to be created in the work class set in the position specified by the integer value. For example:

         WORK TYPE ... POSITION AT 3

2. Commit your changes. When you commit your changes, the work class is added to the SYSCAT.WORKCLASSES view.