DB2 10.5 for Linux, UNIX, and Windows

Scenario: Using data tags to favor activities accessing high priority data

Use work class sets and work actions sets to initially map activities to a high priority service class based on the data that may be touched before the activity starts. If the activity touches lower priority data at run time, remap the activity to a lower priority service subclass using the DATATAGINSC threshold.

In this scenario, before any DML work starts, the activities are presorted based on the estimated data tag to the appropriate service subclass. Any DML statement which the optimizer estimates will touch a table space with a data tag of 1 or 2 will start in WLM_HIGH, any DML statement with an estimated data tag of 5 will start in WLM_MEDIUM and any DLM with an estimated data tag of 9 will start in WLM_LOW. All other DML activity will start in the WLM_HIGH service class. As the activities run, any work starting in the WLM_HIGH service subclass touching a table space associated with a data tag other then 1 or 2 is automatically remapped to the WLM_MEDIUM service class. If the work executing in the WLM_MEDIUM service class touches a table space associated with a tag value of 6, 7, 8, or 9, then it is remapped to the WLM_LOW service class. Work in the WLM_LOW service class remains there for its remaining execution time.

Assume you have already created table spaces and tagged your high priority data with a data tag value of 1 or 2, your medium priority data with a data tag value 5 and your low priority data with a data tag value of 9 by setting the DATA TAG value in the appropriate table spaces.

To map activities to appropriate service subclasses and to remap at runtime when necessary, do the following:

Create a service super class and create 3 service subclasses; WLM_HIGH is to have the best resources assigned, WLM_MEDIUM has lower settings than WLM_HIGH and WLM_LOW has the lowest settings.

  CREATE SERVICE CLASS WLM_DATATIERS
  CREATE SERVICE CLASS WLM_HIGH UNDER WLMDATATIERS SOFT CPU SHARES 5000
  CREATE SERVICE CLASS WLM_MEDIUM UNDER WLMDATATIERS HARD CPU SHARES 3000
  CREATE SERVICE CLASS WLM_LOW UNDER WLMDATATIERS HARD CPU SHARES 2000

Update the default user workload to map its activities to the WLM_DATATIERS service superclass.
```
  ALTER WORKLOAD SYSDEFAULTUSERWORKLOAD SERVICE CLASS WLM_DATATIERS
```

Create a work class set containing work classes that isolate out activities based on the estimated data tag.

   CREATE WORK CLASS SET WLM_DATATAGS_WCS
        (WORK CLASS WLM_DML_HIGH1_WC WORK TYPE DML DATA TAG CONTAINS 1,
         WORK CLASS WLM_DML_HIGH2_WC WORK TYPE DML DATA TAG CONTAINS 2,
         WORK CLASS WLM_DML_MEDIUM_WC WORK TYPE DML DATA TAG CONTAINS 5,
         WORK CLASS WLM_DML_LOW_WC WORK TYPE DML DATA TAG CONTAINS 9,
         WORK CLASS WLM_DML_WC WORK TYPE DML)

Create a work action set containing work actions that map activities to the appropriate service subclasses based on the list of estimated data tags that is received from the optimizer at compile time.

   CREATE WORK ACTION SET WLM_DATATAGS_WAS FOR SERVICE CLASS WLM_DATATIERS
       USING WORK CLASS SET WLM_DATATAGS_WCS
          (WORK ACTION WLM_MAP_HIGH1_WA ON WORK CLASS WLM_DML_HIGH1_WC 
                 MAP ACTIVITY TO WLM_HIGH,
           WORK ACTION WLM_MAP_HIGH2_WA ON WORK CLASS WLM_DML_HIGH2_WC 
                 MAP ACTIVITY TO WLM_HIGH,
            WORK ACTION WLM_MAP_MEDIUM_WA ON WORK CLASS WLM_DML_MEDIUM_WC
                 MAP ACTIVITY TO WLM_MEDIUM
             WORK ACTION WLM_MAP_LOW_WA ON WORK CLASS WLM_DML_LOW_WC
                  MAP ACTIVITY TO WLM_LOW,
              WORK ACTION WLM_MAP_DML ON WORK CLASS WLM_DML_WC
                   MAP ACTIVITY TO WLM_HIGH)

Create a threshold to remap activities from WLM_HIGH to WLM_MEDIUM if they touch data considered medium priority and a threshold to remap activities from WLM_MEDIUM to WLM_LOW.

   CREATE THRESHOLD REMAP_HIGH_TO_MEDIUM FOR SERVICE CLASS WLM_HIGH UNDER WLM_DATATIERS ACTIVITIES
         ENFORCEMENT DATABASE PARTITION 
         WHEN DATATAGINSC NOT IN (1, 2) 
         REMAP ACTIVITY TO WLM_MEDIUM
   CREATE THRESHOLD REMAP_MEDIUM_TO_LOW FOR SERVICE CLASS WLM_MEDIUM UNDER WLM_DATATIERS ACTIVITIES
         ENFORCEMENT DATABASE PARTITION 
         WHEN DATATAGINSC IN (6, 7, 8, 9)
         REMAP ACTIVITY TO WLM_LOW