Session priority

Allow priority specification of requests through workload specification or through a stored procedure.

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

Priority setting within a superclass

The priority of work that is submitted to admission control can be used to control the ordering of admittance to work into the database. The available levels for priority specification are CRITICAL, HIGH, MEDIUM, and LOW.

Example 1:
Within a superclass, consider a scenario where four activities enter the system 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 that the activities need to wait for resources, the activities are queued in the following order:

The queue is based on the priorities of activities, that is, CRITICAL, HIGH, LOW, LOW. For the two LOW priority activities, the activities are ordered in first-in first-out (FIFO) order.
If A4 is released, and if another query enters the system, for example, A5 of MEDIUM priority, the activities are queued in the following order:

Priority setting across superclasses

Across superclasses, activities that are at the front of the superclass queues are admitted based on how underserved a superclass is compared to all other superclasses with queued work.

Example 2:
Assuming that you use the superclass from Example 1 with the four queued activities (A2, A5, A1, and A3), and a second superclass with a single queued activity B1 with LOW priority. Assuming also that the second superclass is more underserved that the first one, the activities would be queued in the following order:

B1 is admitted into the system before A2 because priority is not considered when work is admitted across superclasses.

Priority setting through workload specification

You can do priority customization through workload definitions by specifying the priority level for work mapping to the specified workload. For example, the following DDL creates a workload, PAYROLL, where the user NEWTON executes its work at HIGH priority within the HUMAN_RESOURCES service class.

db2 "CREATE WORKLOAD PAYROLL SESSION_USER('NEWTON') SERVICE CLASS HUMAN_RESOURCES PRIORITY HIGH POSITION AT 1"

Priority setting through a stored procedure

You can use also the WLM_SET_SESSION_PRIORITY() stored procedure to set a session priority for the specified application to the specified level. For example, the following SQL sets the session priority for application 0-2361 to LOW:

db2 "call SYSPROC.WLM_SET_SESSION_PRIORITY(2361, 'LOW')"

Note: Priority execution is strictly adhered to when it is decided which work to run within a superclass when there are resources available. Within a superclass, all CRITICAL priority work runs first, then HIGH, then MEDIUM, and then LOW. A constant stream of high priority work can starve out lower priority work when all resources are being used by the higher priority work.