Product Documentation

Appearance

CREATE WLM RULE ​

Create a workload management (WLM) rule.

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

Only superusers can create, alter, and drop WLM rules.

Query the sys.wlm_pending_rule view to see the rules in the system. Alternatively, use the ybsql \dwr and \dwr+ commands to return information about rules. The output of the \dwr+ command matches the detailed output of the system view. 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.

type

Rule type: prepare, runtime, or completion. See Creating 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. 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 Prepare type.

Parent topic:SQL Commands