Product Documentation

Appearance

CREATE WLM RULE ​

Create a workload management (WLM) rule.

CREATE WLM RULE name [ ( PROFILE name [ options ] ) ]

Only superusers and users who have been granted CONTROL WLM ON SYSTEM can create, alter, and drop WLM rules.

The immediate result of a CREATE WLM RULE command is a pending rule. See Profile Activation for details about the transition of pending rules to active rules. You can query the sys.wlm_pending_rule and sys.wlm_active_rule views to see the rules in the system. Alternatively, use the ybsql \dwr and \dwr+ commands to return similar information. The output of the \dwr+ command matches the detailed output of the system views. The \dwr command returns a list of rules but does not include their JavaScript definitions.

RULE name ​

Use any valid SQL identifier as the rule name. Names longer than 128 bytes are allowed but truncated. See also SQL Identifiers.

PROFILE name ​

A single set of parentheses is required around the profile name and the subsequent list of options (if any). The rule may belong to an existing profile that you name in the command, or a profile that you have not yet created. For example: (profile myprofile)

Alternatively, if you specify null or do not specify a profile, the rule is global and belongs to all profiles: (profile null)

You cannot attach a user-defined rule to the system profiles (default and maintenance).

Options ​

Express each option as a key-value pair and separate the options with commas. You can list options in any order. If you do not specify any options, the result is a WLM rule with default values. However, you must specify the javascript for every rule you create.

type

Rule type: submit, assemble, compile, restart_for_user, restart_for_error, runtime, or completion. See Rule Types and Creating WLM Rules.

rule_order

Rule order, expressed as a number greater than or equal to 1; cannot be null. The default is 100. This value defines the order in which rules are applied, lowest first, highest last. Rules marked as 1 are applied before rules marked as 10 or 100, for example.

enabled

If true, the rule is enabled on creation. The default value is true. This option cannot be null.

superuser

If true, the rule applies to superuser accounts only. The default value is false. This option cannot be null.

javascript

Rule conditions and actions, expressed in JavaScript. This option is required. Use the following format:

javascript $$ <rule_in_javascript> $$

For example:

javascript $$ if (w.database === 'premdb') {w.resourcePool = 'shortquerypool';} $$

By using the $$ notation, you will avoid potential problems with special characters that might otherwise need to be escaped. The default value is "" (no rule condition or action).

version

Version of this rule (can be null).

Examples ​

Create a rule with profile, superuser, and javascript options defined:

premdb=# create wlm rule slowlane 
(profile shortquery, 
superuser true, 
javascript $$ if (w.referencedTables.contains('match')) {w.maximumRowLimit = 1000000;}$$) ;
CREATE WLM RULE

Because no type is specified, this rule will default to the compile type.

The following rule is a runtime rule:

create wlm rule runtime_matchstats_critical_priority
(profile shortquerybias, 
type runtime, 
javascript $$
if (w.referencedTables.contains('matchstats') &&
   w.priority === 'low' ||
   w.priority === 'normal' ||
   w.priority === 'high') {
	w.priority = 'critical';
} $$);
CREATE WLM RULE

Parent topic:SQL Commands