Session priority
Within each service superclass, use session priority to prioritize more important jobs over less important jobs. Session priority can be specified for a group of connections using a workload attribute or for a single connection using a stored procedure. Across superclasses, activities are scheduled based on which service superclasses are the most underserved relative to their resource entitlement.
Priority Levels
- CRITICAL - Highest priority work
- HIGH - High priority work
- MEDIUM - Medium priority work. This is the default priority level for all work if a priority level is not explicitly specified.
- LOW - Low priority work
Higher priority levels should be used with caution. Too many critical and high priority queries may have a negative impact on the performance of lower priority work.
Priority Weighting
Priorities are used to bias the order in which work is admitted within each service superclass. Each priority level has an entitlement to the superclass resources based on their relative weights.
- CRITICAL priority work has a relative weight of 8
- HIGH priority work has a relative weight of 4
- MEDIUM priority work has a relative weight off 2
- LOW priority work has a relative weight of 1
priority level entitlement = (priority level weight / sum of weights for all active priority levels) x (superclass entitlement)
Where any priority level that has work executing or queued is considered to be an active priority level.
If all queries were to have identical resource requirements and there were queries submitted in each priority level, queries would be admitted with a ratio of 8:4:2:1. This means for every 1 low priority query admitted, 2 medium priority, 4 high priority and 8 critical priority would be admitted.
entitlement of medium priority work = (priority level weight / sum of weights for all active priority levels) x (superclass entitlement)
= (2 / 2) x (30%)= 30%
entitlement of low priority work = (priority level weight / sum of weights for all active priority levels) x (superclass entitlement)
= (1 / 5) x (50%)
= 10%
entitlement of high priority work = (priority level weight / sum of weights for all active priority levels) x (superclass entitlement)
= (4 / 5) x (50%)
= 40%
Priority behavior within a superclass
- Activity 1 - A1 with LOW priority
- Activity 2 - A2 with HIGH priority
- Activity 3 - A3 with LOW priority
- Activity 4 - A4 with CRITICAL priority
Assuming all priorities are equally under-served, the activities are queued in the order A4, A2, A1, A3.
Priority behavior across superclasses
Across superclasses, activities are scheduled based on which service superclasses are the most under-served relative to their resource entitlement. Consider if we have 2 service superclasses (A, B) with a 50% resource entitlement each. Currently A is using 100% of the resources. Four queries (Q1, Q2, Q3, Q4) arrive in service superclass A, all marked HIGH priority. These queries queue waiting for available resources. Now a fifth query (Q5) arrives in service superclass B with LOW priority. Since service superclass A is currently using all available resources and B is using none, service superclass B is the most under-served relative to it's resource entitlement. Hence the admission queue order would be Q5, Q1, Q2, Q3, Q4.Even though Q5 is a lower priority and arrived later than the HIGH priority queries it was submitted in a different service superclass than the high priority work, hence it is queued ahead of that work because its service superclass is the most under-served relative to its resource entitlement.
Setting priority for connections based on connection attributes
WORKLOAD
object. Work submitted by any connection mapping to the
WORKLOAD
object will run with the priority level that is specified in the
WORKLOAD
definition. For example, the following DDL defines a
WORKLOAD
object named PAYROLL
that is used to identify connections
from the user NEWTON
. The priority level for this workload is set to
HIGH
. Any work submitted by connections mapping to this workload (which means any
connections from user NEWTON
) will execute with HIGH
priority.
db2 "CREATE WORKLOAD PAYROLL SESSION_USER('NEWTON')PRIORITY HIGH"
Connections re-evalute their WORKLOAD
object associations and detect changes in
WORKLOAD
objects on transaction boundaries. Hence any changes to priority via a
WORKLOAD
object will take effect for existing connections after the completion of
their current transaction.
Setting priority for a specific connection
0-2361
to
LOW:db2 "call SYSPROC.WLM_SET_SESSION_PRIORITY(2361, 'LOW')"
Using the stored procedure, you can change the priority level for work that is currently queued, as well as for all subsequent work performed by the connection. Unlike specifying priority using a WORKLOAD object, priority changes made using the stored procedure take effect immediately.
Session Priority and Service Class Minimum Resource Shares
A service class minimum resource share is the percentage of entitled service class resources that are held in reserve for exclusive use by work running in the service class. If a service class is configured with a minimum resource share, lower priority work running in this service class will be able to take advantage of the reserved resources and may execute ahead of higher priority work running in other service classes.
WORKLOAD TYPE
clause is used during service class creation,
certain workload types will pre-configure one or more subclasses with a non-zero minimum resource
share. For more details refer to Using workload types to simplify service class creation.