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.
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.
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 DB2® service subclass based on the data that is estimated to be accessed.
Agents working for the activity will periodically check if a threshold has been violated on each member, without coordination between 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.
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