Creating WLM Rules ​

Given knowledge of the workloads in your data warehouse, you can write WLM classification rules that define specific scheduling behavior, resource allocation, and other actions that apply to queries at different points in their life cycle (from submission to completion). Rule conditions may look for matches to a particular table referenced in a query or a particular user who is running it. When a query qualifies for the conditions defined in a rule, a given action is taken, such as to place the query in a given resource pool, restrict memory use, or even abort the query.

To create a rule, use one of the following methods:

• Go to Workload Management > Global Rules > +Rule in Yellowbrick Manager. You can use assisted mode or advanced mode to write the logical conditions for rules.

In assisted mode (during step 2 of 3), JavaScript is generated for the rule based on your choices of conditions and actions. Simple logical operators are provided, such as equals, not equals, greater than, less than, and so on. Where appropriate, drop-down lists provide specific choices, such as database names, table names, and names of resource pools. In other cases, you can use regular expressions to write conditions based on pattern matching.

In advanced mode, you can type in the JavaScript logic yourself, making use of the same building blocks that assisted mode provides.

• Use the SQL command CREATE WLM RULE. In this case, you need to know how to write the JavaScript logic for rule conditions and actions (as with advanced mode in Yellowbrick Manager).

Note: Choosing the rule type is a critical step. The type affects when the rule is evaluated during the query lifecyle and determines what planning information is available to the rule. In turn, the rule type determines the kinds of actions that can be taken. For details, see Rule Types and Rule Examples.

Yellowbrick Manager shows a list of rules that are currently in the system. You can also query the sys.wlm_pending_rule view or use the ybsql \dwr command.

Rule Order ​

All rules are evaluated for every query. The order in which they run is controlled by the rule order. Lower-numbered rules are run first. For example, a rule with a rule order of 10 runs before a rule with a rule order of 100. Therefore, a rule with a higher rule order number may override settings that were set by a rule with a lower number.

This override behavior may be set on purpose. For example, consider the following rules:

• Rule 1, order 10:

w.priority = ‘normal’;
 w.requestedMemoryPercent = 10;

• Rule 2, order 100:

if (w.application === ‘SAS’) {
w.priority = ‘high’;
w.requestedMemoryPercent = 50;
}

Both rules set the priority and requestedMemoryPercent values, but the final values set for the query are governed by the last rule to run.

System Rules and Global Rules ​

Some WLM rules are defined for each system-defined profile, and these rules are prefixed with the profile name. For example:

• flex_mapToPenalty
• default_mapToSmall

You cannot remove or modify these rules.

A number of global rules belong to all profiles, and these rules are prefixed with global_. You can remove or modify these rules. A set of global rules exists for each instance you create. Changing a rule for one instance has no effect on the corresponding rule in another instance.

For example, by default the global_defaultRowLimit rule prevents queries run by regular users from returning more than 5,000,000 rows. These queries are aborted and return the following error:

premdb=> select * from newmatchstats;
ERROR:   WLM row limit exceeded

You can increase this limit globally for a given instance by changing the definition of the rule action. For example:

After changing rules, you have to activate the changes before they will take effect, as discussed in Creating WLM Profiles.