Scheduler rules

A plan is a unit of work that is created by the optimizer to handle a query. Each plan is based on the content of a query and on statistics regarding the tables on which the query acts. A single query usually results in a single plan, but occasionally additional, auxiliary plans are generated also.

The scheduler places each plan that it receives into a pool of candidates for execution. When the scheduler detects that there is capacity to execute a plan (for example, because it was notified that the execution of another plan has completed), it selects a plan from this pool. Which plan the scheduler selects is determined by the system's SQB, GRA, and PQE settings, and by the attributes of each plan, for example:
  • Whether it is flagged as being short
  • The resource group with which it is associated
  • Its priority
A scheduler rule is an object that influences the scheduling of plans. Each scheduler rule specifies:
  • Zero or more conditions
  • One action
  • Whether it is to apply to admin plans as well as plans submitted on behalf of other users
The action is carried out for each plan that meets all the specified conditions. If no condition is specified, the action is carried out for all plans.
A scheduler rule can be of one of the following types:
Modifying
A modifying scheduler rule modifies the attributes of or aborts a plan. For example, the following rule changes a plan with normal priority to a plan with low priority: 
IF PRIORITY IS NORMAL THEN SET PRIORITY LOW
Each time the scheduler receives a plan, it evaluates all modifying scheduler rules and carries out the appropriate actions.
Limiting
A limiting scheduler rule limits the number of plans that can run concurrently. For example, the following rule prevents more than one plan associated with a query on tables or objects in the database with the name DB1 from running at a time: 
IF DATABASE IS DB1 THEN LIMIT 1
Each time the scheduler selects a plan for execution, it evaluates all limiting scheduler rules. The plan is executed only if doing so would not exceed a limit imposed by a limiting scheduler rule. Otherwise, the plan waits.

By deft use of scheduler rules, you can exert a high level of control over plan execution. A scheduler rule does not apply to admin plans unless you explicitly specify otherwise when you create the rule.

Scheduler rule conditions

The conditions of a scheduler rule determine whether it applies to a particular plan:
RESOURCEGROUP
The plan's resource group. Usually, this is the resource group of the user who submitted the corresponding query; however, it can be changed by a modifying scheduler rule. The specified resource group must already exist.
PRIORITY
The priority of the plan:
  • CRITICAL
  • HIGH
  • NORMAL
  • LOW
TYPE
The plan type:
LOAD
The plan loads (reads) data from an external table.
UNLOAD
The plan unloads (writes) data to an external table.
GENERATE STATISTICS
The plan generates or updates database statistics
GROOM
The plan removes outdated and deleted records from tables.
UDX
The plan runs a user-defined object.
DATABASE
The database that is to be accessed by the plan. This is the database to which the session is connected. The specified database must already exist.
USER
The user who submitted the query that corresponds to the plan. The specified user must already exist. The specified user cannot be Admin. To include plans associated with Admin jobs, specify the INCLUDING ADMIN option.
TABLE
A user table accessed by the plan. The specified table must already exist. If the database name or schema name are needed to uniquely identify the table, they must be specified as part of the table name.
TAG
A string that is to be associated with and can be used to reference the plan (see Tags).
CLIENT_USER_ID
The user ID under which the application that submitted the corresponding query is running. This value is specified for the session, and is usually set by an application. The value must be specified in single quotes.
CLIENT_APPLICATION_NAME
The name of the application that submitted the corresponding query. This value is specified for the session, and is usually set by an application. The value must be specified in single quotes.
CLIENT_WORKSTATION_NAME
The host name of the workstation on which the application that submitted the corresponding query runs. This value is specified for the session, and is usually set by an application. The value must be specified in single quotes.
CLIENT_ACCOUNTING_STRING
The value of the accounting string. This value is specified for the session, and is usually set by an application. The value must be specified in single quotes.
ESTIMATE
The lower bound, upper bound, or both of the estimated cost of the plan, in seconds:
  • Specify a lower bound using the > or >= operator.
  • Specify an upper bound using the < or <= operator.
If a scheduler rule specifies both a lower and an upper bound, the lower bound must be less than the upper bound.

Actions of a modifying scheduler rule

A modifying scheduler rule can specify the following actions:
SET SHORT
Set the SQB flag for the plan, regardless of its cost estimate and the threshold set by the host.schedSQBNominalSecs registry setting.
SET NOT SHORT
Do not set the SQB flag for the plan, regardless of its cost estimate and the threshold set by the host.schedSQBNominalSecs registry setting.
ADD TAG
Add a tag to this plan. Each plan can have any number of tags associated with it.
SET PRIORITY
Change the priority of the plan. This action ignores user and group limits on priority.
SET ESTIMATE
Set the cost estimate of the plan, in seconds. This action overrides the estimate calculated by the planner. It does not affect the SQB flag.
INCREASE PRIORITY
Increase the priority of the plan by one level, for example from NORMAL to HIGH. It ignores an attempt to increase a CRITICAL priority, and ignores user and group limits on priority.
DECREASE PRIORITY
Decrease the priority of the plan by one level, for example from NORMAL to LOW. It ignores an attempt to decrease a LOW priority, and ignores user and group limits on priority.
EXECUTE AS RESOURCEGROUP
Execute the plan as if it belonged to the specified resource group.
ABORT
Abort the plan. This provides a way to temporarily disallow jobs that involve particular tables, database, or users, for example, during maintenance. The specified message is passed to the user who issued the corresponding query.

Using scheduler rules with SQB, GRA, and PQE

A scheduler rule can modify the following plan attributes:
  • Resource group
  • Priority
  • Cost estimate
  • Whether it is short
  • Tags
This provides you with a way to classify and manipulate plans in a way that influences the SQB, GRA, and PQE mechanisms:
SQB
You can specify whether a plan is short. For example, the following scheduler rule causes all plans that run user-defined objects to be regarded as being short, regardless of their cost estimates:
IF TYPE IS UDX THEN SET SHORT
GRA
You can associate plans with particular resource groups. For example, the following scheduler rules associate plans with resource groups based on the databases that they access:
IF DATABASE IS DB1 THEN EXECUTE AS RESOURCEGROUP RSG_A
IF DATABASE IS DB2 THEN EXECUTE AS RESOURCEGROUP RSG_A
IF DATABASE IS DB3 THEN EXECUTE AS RESOURCEGROUP RSG_B
IF DATABASE IS DB4 THEN EXECUTE AS RESOURCEGROUP RSG_A
IF DATABASE IS DB5 THEN EXECUTE AS RESOURCEGROUP RSG_B
By configuring resource groups RSG_A and RSG_B appropriately, you can make resource allocation dependent on which databases are being accessed. Note that the resource groups RSG_A and RSG_B do not have to have any users assigned to them, but can be used exclusively as a means to allocate system resources.
PQE
You can modify plan priority. For example, the following scheduler rules modify the priority of plans based on the tables that they access:
IF TABLE IS T1 THEN SET PRIORITY LOW
IF TABLE IS T7 THEN DECREASE PRIORITY

Scheduler rule evaluation order

Scheduler rules are evaluated in alphanumeric order (a-z, A-Z, 0-9) according to their names. For example, the following rules might both be defined:
Rule name            Rule
r2_decrease_normal   IF PRIORITY IS NORMAL THEN DECREASE PRIORITY
r3_low_to_normal     IF PRIORITY IS LOW THEN SET PRIORITY NORMAL
When a plan with normal priority is processed, the rule with the name r2_decrease_normal, which is processed first, causes the priority of the plan to be decreased from NORMAL to LOW. Then, the rule with the name r3_low_to_normal causes the priority of the plan to be set back to NORMAL.

Tags

A tag is a string that is associated with and used to refer to a particular session or plan:
Session tag
A session tag applies to a particular session and to all the plans that are within the scope of that session. It is set by specifying the ADD TAG parameter for an ALTER SESSION command.
Plan tag
A plan tag applies to a particular plan. It is set by specifying the ADD TAG parameter for a CREATE SCHEDULER RULE command.
A condition (but no more than one) of a scheduler rule can refer to a tag to influence plan scheduling. For example:
  • To prevent an end-of-month test from sapping performance, you might add a tag with the name eom_test to the corresponding session, and create a scheduler rule with a condition that refers to that tag and that automatically decreases the priority of any plan that originates from that session:
    IF TAG IS eom_test THEN DECREASE PRIORITY
  • To prevent a session in which extract, transform, and load (ETL) jobs run from sapping performance, you might add a tag with the name etl to that session, and create a scheduler rule with a condition that refers to that tag and that automatically set the priority of any plan that originates from that session to LOW:
    IF TAG IS etl THEN SET PRIORITY LOW
A scheduler rule can be used to add a tag to each plan that meets the conditions it specifies. For example, a scheduler rule might add the tag user_is_jill to each plan for which the associated user is jill:
IF USER IS jill THEN ADD TAG user_is_jill
This tag can then be referenced by subsequent scheduler rules. This is helpful when you have a long or complex condition that is employed by several scheduler rules.
By creating several scheduler rules each of which adds the same tag to each plan that meets its conditions, you can specify a series of conditions that behave as if linked by a logical OR operator. For example, the following three rules, when evaluated in the order shown, cause the limit to be set to 2 for all plans for which either the database is reportdb or the user is joe:
IF DATABASE IS reportdb THEN ADD TAG no_more_than_2
IF USER IS joe THEN ADD TAG no_more_than_2
IF TAG IS no_more_than_2 THEN LIMIT 2

Client information

The application that submitted the query that is associated with a plan is called the plan's client. The following information about the client can be specified for a session and referenced by a scheduler rule condition:
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.
These information fields can be set by either:
  • An administrator, by issuing the nzsql SET command
  • An application program, by means of the ODBC API (see IBM® Netezza® ODBC, JDBC, OLE DB, and .NET Installation and Configuration Guide)
Each value can be up to 1024 characters.

Scheduler rule security

To create a scheduler rule, you must either be the admin user or your user account must have the Scheduler Rule privilege. The admin user and a user with the Scheduler Rule privilege can also list, drop, alter, deactivate, or reactivate any rule, regardless of who created or owns it.

The owner of a scheduler rule is, by default, the user who created it; however, ownership can be reassigned to a different user. The owner of a scheduler rule can list, drop, alter, deactivate, or reactivate that rule, regardless of which privileges that user has been granted.