Specifying priorities

When a user opens a database session, that session is assigned a priority. The session priority determines the priority of all jobs that are submitted during the session. For example, if a session is assigned the priority HIGH, all jobs submitted during that session also have the priority HIGH, as will their corresponding plans.

The priority of a particular session (and its jobs and their plans) is determined by several factors:
  • System settings
  • Settings for the user who submitted the corresponding job
  • Settings for the user groups to which that user belongs, if any
  • Settings for the session used to submit the corresponding job
In addition, scheduler rules can change plan priorities when certain conditions are satisfied by the attributes of the corresponding job.
The following settings determine the default and maximum priorities for a session:
  • Issue the SET SYSTEM DEFAULT command to set the following parameters:
    DEFPRIORITY
    The system default priority, which is the priority that is assigned to any job for which a priority is not set by other means. The default is NORMAL.
    MAXPRIORITY
    The system maximum priority, which is the highest level priority that can be set for any job. The default is CRITICAL.
  • For each user, you can issue the CREATE USER or ALTER USER command to set the following parameters:
    DEFPRIORITY
    The user's default priority, which is the priority that is assigned to any job that is submitted by the user.
    MAXPRIORITY
    The user's maximum priority, which is the highest level priority that the user can set for any submitted job.
  • For each user group, you can issue the CREATE GROUP or ALTER GROUP command to set the following parameters:
    DEFPRIORITY
    The group's default priority, which is the priority that is assigned to any job that is submitted by a member of the user group.
    MAXPRIORITY
    The group's maximum priority, which is the highest level priority that a member of the user group can set for any submitted job.
The first of the following rules to apply determines which priority is assigned to a session:
  1. Was a default priority other than NONE assigned to the user? If so, that priority is assigned to the session.
  2. Is the user a member of at least one user group for which a default priority other than NONE was specified? If so, the lowest of those default priorities is assigned to the session.
  3. The system default priority is assigned to the session.
If the determined priority would exceed the maximum priority for the user, the maximum priority is assigned instead. The first of the following rules to apply determines the maximum priority for a particular user:
  1. Was a maximum priority other than NONE assigned to the user? If so, that priority or the system maximum priority, whichever is lower, is the user's maximum.
  2. Is the user a member of at least one user group for which a maximum priority other than NONE was specified? If so, the lowest of those default priorities and of the system maximum priority is the user's maximum.

A user can issue the ALTER SESSION command to change the session priority. This affects all jobs that are currently running and that are submitted during the remainder of the session.

In addition, a scheduler rule can change the priority of the plans of a job when certain conditions are met. The priority that is set by a scheduler rule is not subject to any of the maximum-priority restrictions that are imposed upon a particular user or user group, or by the system maximum priority. You can create scheduler rules that use any combination of the following criteria to override the priority determined by other means:
Which user submitted the corresponding job
You can use scheduler rules to override a plan's priority assignment based on the submitting user. For example, the following scheduler rule sets the priority of all plans for jobs submitted by the user bob to CRITICAL, regardless of any priority restrictions imposed upon bob directly, by means of a user group, or by the system maximum priority:
IF USER IS bob THEN SET PRIORITY CRITICAL
The contents of the client information fields
An administrator or application program can set the following client information fields in a query:
User ID
The user ID under which the client is running.
Application name
The name of the client.
Workstation name
The host name of the workstation on which the client runs.
Accounting string
The value of the accounting string from the client information that is specified for the session.
You can use scheduler rules to assign a priority based on the contents of these fields (see Client information). For example, the following scheduler rule increases the priority of all plans for jobs submitted by the application named Cognos:
IF CLIENT_APPLICATION_NAME IS Cognos THEN INCREASE PRIORITY
Cost estimates
For each plan, the optimizer calculates the expected cost of processing that plan. You can use scheduler rules to assign a priority based on the calculated cost estimate. For example, the following scheduler rules modify plans' priorities based on the plans' cost estimates:
IF ESTIMATE < 4 THEN SET PRIORITY NORMAL
IF ESTIMATE >= 4 ESTIMATE < 30 THEN SET PRIORITY HIGH
IF ESTIMATE >= 30 THEN SET PRIORITY LOW
The database that is to be accessed
You can use scheduler rules to assign or change plan priorities based on which database each plan accesses. For example, the following scheduler rules decreases the priority of all plans that access database dbx1:
IF DATABASE IS dbx1 THEN DECREASE PRIORITY
The table that is to be accessed
You can use scheduler rules to assign or change plan priorities based on which table each plans accesses. For example, the following scheduler rule decreases the priority of all plans that access table tab1 or tab2:
IF TABLE IN (tab1,tab2) THEN DECREASE PRIORITY
Custom tags
You can add any number of tags to sessions (see Tags). All the plans that are within the scope of that session receive the same tag. You can also create scheduler rules that add tags directly to all plans that meet the conditions specified by the rule. You can then use scheduler rules to assign or change plan priorities based on these tags. For example, the following scheduler rule increases the priority of all plans for which the specified tags have been set:
IF TAG IS eom THEN INCREASE PRIORITY
Important: Use caution when you are assigning the critical and high priority. If you assign too many jobs to the high or critical priority, you can bring normal and low priority work to a standstill.