Scenario: Remapping incorrectly mapped queries through priority aging

The problem: You may have mapped expensive activities based on estimated SQL cost to a lower priority service subclass so that these activities do not impact the performance of less expensive, shorter activities. Such a mapping can be accomplished by defining a work action set at the service superclass level. However, if the estimated SQL cost is incorrect because of statistics that are out of date, for example, an expensive activity might be mapped incorrectly to a high priority service subclass where it begins to consume an excessive amount of resources, at the cost of all other high priority activities.

The solution: You can use the wlmtierstimerons.db2 sample tiering script to configure your data server with a tiered configuration that evaluates incoming activities according to their estimated cost and maps them to one of three service subclasses, each with different shares of CPU. If an activity consumes too much processor time, your data server dynamically lowers the priority of the activity during its lifetime by remapping it between performance tiers. This dynamic process of remapping activities to lower their priority is also referred to as priority aging.

After an activity has been mapped to its initial service class and begins executing, the CPUTIMEINSC in-service-class threshold is used by the script to control the amount of processor time an activity can consume. If the activity violates the threshold by using the maximum amount of allowed processor time, a REMAP ACTIVITY action is triggered which moves the activity to a service subclass with lower agent priority. The activity can be remapped in response to processor time consumption until it executes the lowest priority service subclass where it will continue until it completes or you intervene manually.

An event monitor record is logged every time an activity is remapped. If you want to collect additional information about remapped activities to investigate further, you can add the COLLECT ACTIVITY DATA clause to the ALTER THRESHOLD statement in the wlmtiersdefault.db2 script. Simply rerun the script for the change to take effect.

After running the workload for a period of time, you can use the WLM_GET_SERVICE_SUBCLASS_STATS table function to see how many activities were remapped between the service subclasses:

SELECT substr(service_superclass_name,1,21) AS superclass,
       substr(service_subclass_name,1,21) AS subclass,
       substr(char(coord_act_completed_total),1,10) AS completed,
       substr(char(act_remapped_in),1,10) AS remapped_in,
       substr(char(act_remapped_out),1,10) AS remapped_out,
       substr(char(last_reset),1,19) AS last_reset
FROM table( WLM_GET_SERVICE_SUBCLASS_STATS(
                CAST(NULL AS VARCHAR(128)),
                CAST(NULL AS VARCHAR(128)),
                -2 )
          ) AS TF_subcls_stats@

  SELECT SUBSTR(WORKLOAD_NAME,1,19) AS WL_NAME,
         COORD_ACT_LIFETIME_AVG,
         COORD_ACT_LIFETIME_STDDEV
  FROM TABLE(WLM_GET_WORKLOAD_STATS
        (CAST(NULL AS VARCHAR(128)), -2))
         AS WLSTATS
  ORDER BY WL_NAME@

SUPERCLASS            SUBCLASS              COMPLETED  REMAPPED_IN REMAPPED_OUT LAST_RESET
--------------------- --------------------- ---------- ----------- ------------ -------------------
SYSDEFAULTSYSTEMCLASS SYSDEFAULTSUBCLASS    0          0           0            2008-10-06-20.59.27
SYSDEFAULTMAINTENANCE SYSDEFAULTSUBCLASS    3          0           0            2008-10-06-20.59.27
SYSDEFAULTUSERCLASS   SYSDEFAULTSUBCLASS    0          0           0            2008-10-06-20.59.27
WLM_TIERS             SYSDEFAULTSUBCLASS    0          0           0            2008-10-06-20.59.27
WLM_TIERS             WLM_SHORT             651        0           5            2008-10-06-20.59.27
WLM_TIERS             WLM_MEDIUM            36         5           7            2008-10-06-20.59.27
WLM_TIERS             WLM_LONG              16         7           0            2008-10-06-20.59.27

  7 record(s) selected.

For this scenario, you should see relatively few activities being remapped between service subclasses, because activities should almost always be mapped to the appropriate service subclass initially, based on estimated cost. If you notice that activities typically are being completed only in the WLM_SHORT or the WLM_LONG service class, you can adjust the estimated cost values used by the ALTER WORK CLASS SET statement in the script to improve the mapping of activities across service class tiers, so that shorter activities are mapped to the WLM_SHORT_DML_WC work class and longer activities are mapped to the WLM_MEDIUM_DML_WC or the WLM_LONG_DML_WC work class. If you notice that most of the activities are being remapped, you can increase the threshold values used in the ALTER THRESHOLD statements to improve the initial mapping of activities to service subclasses. After your changes are complete, rerun the wlmtierstimerons.db2 script to make them effective.