Scenario: Controlling resource intensive business intelligence reports with priority aging

The problem: There is a business intelligence report which any end user can run and which is very expensive. Anytime the report runs, it compromises the performance of the system. The front end tool used to generate the report does not set any client information that could be used to identify the report in advance which would permit you to map it to a low priority service class using a workload.

The solution: You can use the wlmtiersdefault.db2 sample tiering scripts to configure your data server with a tiered configuration that dynamically lowers, or ages, the priority of processor intensive activities during their lifetime in order to prevent compromising data server performance for all other users. After a workload initially maps all work to a high priority service subclass, the expensive reports are detected by the CPUTIMEINSC in-service-class threshold based on the amount of processor time consumed. If an activity violates the CPUTIMEINSC threshold by using the maximum amount of allowed processor time, a REMAP ACTIVITY moves the activity to a lower priority service subclass. The activity can be remapped in response to processor time consumption again until it executes in the lowest priority service subclass where it will continue until it completes or you intervene manually. Other activities which do not exceed the thresholds continue to run in the high priority service subclass, where they receive higher agent priority.

An event monitor record is logged every time an activity is remapped, if you created a threshold violations event monitor. 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.53.47
SYSDEFAULTMAINTENANCE SYSDEFAULTSUBCLASS    3          0           0            2008-10-06-20.53.47
SYSDEFAULTUSERCLASS   SYSDEFAULTSUBCLASS    0          0           0            2008-10-06-20.53.47
WLM_TIERS             SYSDEFAULTSUBCLASS    0          0           0            2008-10-06-20.53.47
WLM_TIERS             WLM_SHORT             999        0           35           2008-10-06-20.53.47
WLM_TIERS             WLM_MEDIUM            19         35          16           2008-10-06-20.53.47
WLM_TIERS             WLM_LONG              16         16          0            2008-10-06-20.53.47

  7 record(s) selected.

If you notice that no or only very few activities are being remapped to the lower priority service subclasses, decrease the CPUTIMEINSC threshold value and the check interval used by the ALTER THRESHOLD statements in the script to improve the mapping of activities across service class tiers according to business priority. If most or almost all activities are being remapped to the lower priority service subclasses, increase the CPUTIMEINSC threshold value and the check interval for the ALTER THRESHOLD statements to permit more activities to complete with higher priority. After your changes are complete, rerun the wlmtiersdefault.db2 script to make them effective.