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.
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
javascript
for every rule you
create. - type
- Rule type:
submit
,assemble
,compile
,restart_for_user
,restart_for_error
,runtime
, orcompletion
. See Rule Types and Creating WLM Rules. - rule_order
- Rule order, expressed as a number greater than or equal to
1
; cannot benull
. The default is100
. This value defines the order in which rules are applied, lowest first, highest last. Rules marked as1
are applied before rules marked as10
or100
, for example. - enabled
- If
true
, the rule is enabled on creation. The default value istrue
. This option cannot benull
. - superuser
- If
true
, the rule applies to superuser accounts only. The default value isfalse
. This option cannot benull
. - javascript
- Rule conditions and actions, expressed in JavaScript. This option is required. Use
the following format:
For example:javascript $$ <rule_in_javascript> $$
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
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.
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