CREATE SCHEDULER RULE

Use the CREATE SCHEDULER RULE command to define a new scheduler rule.

Syntax

CREATE [OR REPLACE] SCHEDULER RULE <rule_name> [INCLUDING ADMIN] AS <rule>

Where <rule> represents:

[IF <condition> [<condition> ... ] THEN] {LIMIT <max_plans> [PER USER] | <action>}

Where <condition> represents:

RESOURCEGROUP {IS [NOT] <rsg_name> | [NOT] IN (<rsg_name>, ...)} |  
PRIORITY {IS [NOT] <priority> | [NOT] IN (<priority>, ...)} |
TYPE {IS [NOT] <plan_type> | [NOT] IN (<plan_type>, ...)} |  
DATABASE {IS [NOT] <db_name> | [NOT] IN (<db_name>, ...)} |  
USER {IS [NOT] <user_name> | [NOT] IN (<user_name>, ...)} |  
TABLE {IS [NOT] <table> | [NOT] IN (<table>, ...)} |  
TAG {IS [NOT] <tag_value> | [NOT] IN (<tag_value>)} |  
CLIENT_USER_ID {IS [NOT] '<string>' | [NOT] IN (<string>, ...)} |  
CLIENT_APPLICATION_NAME {IS [NOT] '<string>' | [NOT] IN (<string>, ...)} |
CLIENT_WORKSTATION_NAME {IS [NOT] '<string>' | [NOT] IN (<string>, ...)} |
CLIENT_ACCOUNTING_STRING {IS [NOT] '<string>' | [NOT] IN (<string>, ...)} |
ESTIMATE { < | <= | >= | > } <seconds>

Where action represents:

LIMIT <max_plans> [PER USER]
SET [NOT] SHORT | 
SET ESTIMATE <seconds> | 
ADD TAG <tag_value> | 
SET PRIORITY {LOW | NORMAL | HIGH | CRITICAL} |
{INCREASE | DECREASE} PRIORITY |  
EXECUTE AS RESOURCEGROUP <rsg_name> |
ABORT '<message_for_client>'

Inputs

Table 1. Inputs
Input	Description
OR REPLACE	If a rule with the specified name does not already exist, a new rule is created regardless of whether the OR REPLACE phrase is specified. However, if a rule with the specified name already exists and the OR REPLACE phrase is: Specified, that rule is overwritten. Not specified, an error message is issued and the rule is not overwritten.
<rule_name>	The name of the rule that is to be created. This name must be unique among global objects, that is, it cannot be shared by another scheduler rule, or by any database, group, or user.
INCLUDING ADMIN	The rule is to apply also to admin plans.
<option>	One of the options described in Table 2.
<value>	One of the values described in Table 2.
ESTIMATE	A bound to the estimated cost of the plan, in seconds. If a scheduler rule specifies both a lower and an upper bound, the lower bound must be less than the upper bound.
<seconds>	A number of seconds.
LIMIT	Set the maximum number of plans that meet the conditions specified by the scheduler rule that can run concurrently. For example: The following rule prevents more than two plans from users that belong to the resource group RG1 from running simultaneously: `IF RESOURCEGROUP IS RG1 THEN LIMIT 2` The following rule prevents more than one plan from database DB1 from running simultaneously: `IF DATABASE IS DB1 THEN LIMIT 1` Setting a limit to 0 prevents all plans from running. This can be used to halt all plans that meet certain criteria.
PER USER	The specified limit applies to each user. For example, the following rule allows each user to run at most two plans: `LIMIT 2 PER USER` This is equivalent to creating a rule of the following form for each user: `IF USER IS <username> THEN LIMIT 2`
SET SHORT	Set the SQB flag of the plan, that is, the plan is flagged as belonging to a short query. This is done regardless of its cost estimate and the threshold set by the host.schedSQBNominalSecs attribute.
SET ESTIMATE	Set the cost estimate, in seconds, of the plan. This overrides the estimate calculated by the planner, but does not affect the setting of the plan's SQB flag, even if it falls below the threshold set by the host.schedSQBNominalSecs attribute.
ADD TAG	Add the specified tag to the plan.
SET PRIORITY	Set the priority of a plan to the specified value. If a maximum priority was set for the plan's user or group by means of a SET SYSTEM DEFAULT command, that maximum priority is ignored.
INCREASE or DECREASE PRIORITY	Increase or decrease the priority of a plan by one level. If a maximum priority was set for a the plan's user or group by means of a SET SYSTEM DEFAULT command, that maximum priority is ignored. An attempt to decrease a LOW priority or increase a CRITICAL priority is ignored.
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 queries that involve particular tables, database, or users, for example, during maintenance. The specified message is passed to the user who issued the query that is associated with the aborted plan.

Table 2. Options and values
Option	Value
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 job that corresponds to the plan. The specified user must already exist. The specified user cannot be the admin user. To include plans associated with admin jobs, specify the INCLUDING ADMIN option.
TABLE	A user table that is accessed by the plan. You cannot specify a view or a row-secure table. 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. At most one TAG condition can be specified for a scheduler rule.
CLIENT_USER_ID¹	The user ID under which the application that submitted the corresponding query is running.
CLIENT_APPLICATION_NAME¹	The name of the application that submitted the corresponding query.
CLIENT_WORKSTATION_NAME¹	The host name of the workstation on which the application that submitted the corresponding query runs.
CLIENT_ACCOUNTING_STRING¹	The value of the accounting string. This value is specified for the session.
¹ The must be specified in single quotes, and must conform to the format that resulted when the value was set. For example: Because the value specified for the following SET command is not in single quotes, it is converted to the default case: `SET CLIENT_USER_ID TO Victoria` If the default case is uppercase, a scheduler rule that references this client ID must specify it as `'VICTORIA'`. Because the value specified for the following SET command is in single quotes, it is not converted to the default case: `SET CLIENT_USER_ID TO 'Victoria'` A scheduler rule that references this client ID must specify it as `'Victoria'`.

Outputs

Table 3. CREATE SCHEDULER RULE outputs
Output	Description
`CREATE SCHEDULER RULE`	The system returns this message if the command completes successfully.
`ERROR`	If an attempt to create a scheduler rule fails, the system returns an error message that describes the reason for the failure, for example: `ERROR: CREATE SCHEDULER RULE: object RULE1 already exists as a SCHEDULER RULE.`

Privileges

You must be the admin user or must have the Scheduler Rule privilege.

Usage

The following examples provide sample usage:

To create a scheduler rule with the name cube_builder_1 that allows only one plan from a session with the tag cube to run at any one time:
```
MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE cube_builder_1 AS IF TAG IS cube THEN LIMIT 1;
```
To create a scheduler rule with the name r23_eom1 that decreases the priority of any plan that originates from a session with the tag eom:
```
MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE r23_eom1 AS IF TAG IS eom THEN DECREASE PRIORITY;
```
To create a scheduler rule with the name r23_up1 that increases the priority of any plan that has a cost estimate between 5 and 12 second:
```
MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE r23_up1 AS
  IF ESTIMATE >= 5 ESTIMATE < 12 THEN INCREASE PRIORITY;
```

The following commands create scheduler rules that ensure that the number of plans that generate or update database statistics is limited but that such plans run quickly:

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE genstats_limit AS
  IF TYPE IS GENERATE STATISTICS THEN LIMIT 2;

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE genstats_prio AS
  IF TYPE IS GENERATE STATISTICS THEN INCREASE PRIORITY;

The following commands create scheduler rules that use the contents of various client information fields to set the priorities of the plans that belong to sessions:

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE a1_default_prio_report AS
  IF CLIENT_APPLICATION_NAME IS Cognos THEN SET PRIORITY NORMAL;

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE a2_top_prio_report AS
  IF CLIENT_ACCOUNTING_STRING IS CEO_report THEN SET PRIORITY CRITICAL;

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE a3_high_prio_report AS
  IF CLIENT_ACCOUNTING_STRING IN ('weekly_report', 'daily_report') 
  THEN SET PRIORITY HIGH;

MYDB.MYSCH(USER)=>
  CREATE SCHEDULER RULE a4_low_prio_user AS
  IF CLIENT_USER_ID IS sikspak THEN SET PRIORITY LOW;