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.

Attention: This parameter is available only in Db2® Version 11.5 Mod Pack 2 and later versions.

Priority Levels

The priority of work submitted in a service superclass can be set to one of the following 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

Work will be admitted within each service superclass to target an average resource usage for each priority level based on these relative weights. The resource entitlement for each priority level can be computed as:

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.

Example 1: If a service superclass with an entitlement of 30% of the database resources has only medium priority work executing in it, the medium priority work is entitled to the full superclass entitlement of 30%.

entitlement of medium priority work = (priority level weight / sum of weights for all active priority levels) x (superclass entitlement) 
= (2 / 2) x (30%)= 30%

Example 2: If a service superclass with an entitlement of 50% has high and low priority work executing in it, the entitlements for each priority level are as follows:

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

Within a service superclass, activities are scheduled in priority order subject to the weightings discussed previously. Consider a scenario where 100% of the database resources are currently in use and four activities enter a superclass in the following order:

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

Priority can be customized for connections with a specific set of attributes (for example application name, session user, and so on) by specifying the priority level as part of the definition of a 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

You can modify the priority for a specific application using the WLM_SET_SESSION_PRIORITY() stored procedure. The procedure takes as input an application handle and the new priority level for that application. For example, the following SQL sets the session priority for application 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.

For example, consider a configuration where 2 service subclasses A and B each have 1000 resource shares with a configured minimum resource share of 100%. In such a configuration each service class is entitled to 50% of the superclass resources, and neither service subclass is allowed to use the resources in the other since the entire entitlement is reserved. With this configuration, if a user chooses to run all high priority work in subclass A and low priority work in subclass B they will still see an equal division of resources between the low and high priority work because the high priority work running in subclass A is not allowed to access the reserved resources in subclass B.

Note: When the 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.