Remapping activities between service subclasses

You enable remapping by specifying a REMAP ACTIVITY action with CREATE and ALTER THRESHOLD statements. The remap action, when it is triggered by a threshold violation, moves an activity from one service subclass to another service subclass under the same superclass.

Before you begin

In order to be able to remap to another service subclass, the target service subclass must exist under the same service superclass as the original service subclass of the activity. Either the target or original service subclass can be the default subclass of the superclass. The REMAP ACTIVITY action cannot be applied to service subclasses under the default system class, default maintenance class or default user class.

About this task

The REMAP ACTIVITY action will move an activity to a different service subclass within the same service superclass. Remapping is available with any of the in-service-class thresholds such as CPUTIMEINSC and SQLROWSREADINSC. You use this dynamic process of remapping activities to lower their priority over time, which is also known as priority aging. Lowering the priority of some activities over time can free up system resources, which can then be applied to other activities of higher business importance.

You can use the REMAP ACTIVITY action with the DATATAGINSC in-service-class threshold which maps an activity to a different service subclass, based on the data that is estimated to be accessed.

You can use the REMAP ACTIVITY action with the ACTIVITYTOTALRUNTIMEINALLSC in-all-service-class threshold, which maps an activity to a different service subclass, based on the amount of time that an activity is running.

Agents working for the activity will periodically check if a threshold has been violated on each member. Coordination of the REMAP ACTIVITY action between members depends on the threshold:
  • For an in-service-class threshold violation on a member, when any one agent detects an a threshold violation on a member, the agent triggers the REMAP ACTIVITY action for the activity on the member and then remaps itself to the target service subclass. At this point, the activity is considered remapped. All other agents working for the activity on the same member will remap to the target service subclass when they detect that the activity has been remapped.
  • For the ACTIVITYTOTALRUNTIMEINALLSC threshold, the remapping is coordinated across all partitions. In other words, when an activity is moved on a member due to an ACTIVITYTOTALRUNTIMEINALLSC threshold violation, the activity is moved on all members.
When any one agent detects an in-service-class threshold violation on a member, this agent triggers the REMAP ACTIVITY action for the activity on the member and then remaps itself to the target service subclass, after which the activity is considered remapped. All other agents working for the activity on the same member will remap to the target service subclass when they detect that the activity has been remapped.


Restrictions

The target service subclass cannot be the same as the original service subclass; you must remap to a different service subclass first before remapping to the original one.

If an activity is remapped to a disabled service subclass, the activity is treated as if it has been rejected by the disabled subclass and an error message -4714 is returned to the client.

Procedure

  1. Identify those activities which you want to control through priority aging. A tiered setup consists of service subclasses under the same service superclass that have in-service-class thresholds with REMAP ACTIVITY threshold actions defined on them. You can refer to the basic priority aging example and to the sample scenarios as a starting point:
    1. Priority aging of ongoing work
    2. Scenario: Controlling resource intensive business intelligence reports with priority aging
    3. Scenario: Remapping incorrectly mapped queries through priority aging
  2. Select the different service subclasses that activities will be mapped to. This includes both the the service subclass an activity is originally mapped to when activity execution starts, and any other service subclass or classes the activity will be remapped to during its lifetime. For more information on service classes, including on how to create them, see Resource assignment with service classes.
  3. Create or alter your thresholds for controlling activities. For more information on thresholds, see Priority aging of ongoing work.
    1. Define your in-service-class thresholds so that they include a REMAP ACTIVITY action, which is triggered when the threshold is violated. Note that an in-service-class threshold applies to and is affected by an activity only while the activity is mapped to the associated service subclass; affected counters and timers are reset after remapping. Consider if a threshold violation record should be logged each time an activity remaps. These records provide information about which service classes an activity spent time executing in, which you can use for performance analysis. Note that logging threshold violation records can begin consuming substantial amounts of disk space, if remapping of activities between service subclasses is a common occurrence.
    2. You can also define any thresholds that you want to apply to the lifetime of the activity, but note that only thresholds from the first service subclass that the activity is originally mapped to continue to apply throughout the lifetime of the activity. If you also define any thresholds on any of the service subclasses that an activity is later remapped to, they do not apply.
  4. Commit your changes.
    When you commit your changes, your thresholds are added to the SYSCAT.THRESHOLDS view.
  5. Allow your data server to execute the activities you are targeting with your in-service-class thresholds and monitor their progress during their lifetime. Activities will stay in the their original service subclass during execution as long as they do not violate an in-service-class threshold. As in-service-class thresholds are violated during activity execution, activities will trigger a REMAP ACTIVITY action that dynamically remaps them to different service subclasses. Once remapped, the activities continue execution and are now controlled by the resource constraints you have placed on the target service subclass.
  6. If necessary, refine your approach to priority aging to reach your stated performance goals.

Example

The following example creates a simple three-tiered setup that lowers, or ages, the priority of ongoing activity over time. Three service subclasses under a single superclass A provide the execution environment in which all queries must run. Assume that the default user workload maps incoming queries to service subclass A1, which is a high-priority subclass intended to permit shorter running queries to execute quickly. A medium-priority service subclass A2 is intended to permit longer running queries to execute, although with more stringent resource controls. Service subclass A3 provides containment for any very large queries that take an excessive amount of processor time to complete.

Three thresholds provide control over the ongoing resource consumption of queries. A query is permitted to execute in the high-priority service subclass A1 only as long as it requires less than one minute of processor time to complete. After a minute of processor time has been consumed, threshold T1 automatically remaps the activity to subclass A2, where it can continue executing as long as it consumes less than 10 minutes of processor time. If the query still has not completed after 10 minutes of consuming processor time, threshold T2 remaps the activity to the lowest priority service subclass, A3. Queries in subclass A3 are permitted to continue indefinitely, although an event monitor record is logged and activity data with details is collected when the processor time used exceeds 1 hour.

CREATE SERVICE CLASS A
CREATE SERVICE CLASS A1 UNDER A
CREATE SERVICE CLASS A2 UNDER A
CREATE SERVICE CLASS A3 UNDER A

CREATE THRESHOLD T1 FOR SERVICE CLASS A1 UNDER A
  ACTIVITIES ENFORCEMENT MEMBER
  WHEN CPUTIMEINSC > 1 MINUTE REMAP ACTIVITY TO A2

CREATE THRESHOLD T2 FOR SERVICE CLASS A2 UNDER A
  ACTIVITIES ENFORCEMENT MEMBER
  WHEN CPUTIMEINSC > 10 MINUTES REMAP ACTIVITY TO A3

CREATE THRESHOLD T3 FOR SERVICE CLASS A3 UNDER A
  ACTIVITIES ENFORCEMENT MEMBER
  WHEN CPUTIMEINSC > 1 HOUR LOG EVENT MONITOR RECORD
  COLLECT ACTIVITY DATA WITH DETAILS
  CONTINUE