BRL Factual Information

Reference:

BRL is capable of representing several different types of information within a single rule file or rule:

These facts are described in the following sections. BRL facts are case sensitive and must be used exactly as shown. These sections give examples based on the following rule:

RULE        Check Conditions
IF          Select asterisk
AND         Day of the Week <5
AND         TableName IS XXX
AND         Current Date = "20080709"
THEN        DBA Rules Concluded


Reference: Simple Facts

A simple fact is a straightforward expression that requires only a true or false answer. An example of a simple fact is "Select asterisk."



Reference: Numeric Facts

Numeric data lets you compare values, create computations, and assign numeric data to variables. BRL recognizes that you are specifying a numeric data type by the presence of one of the reserved relational, numerical, or assignment operators within a supporting condition (antecedent, or IF statement).

Relational Operators

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

< >

Not equal

=

Equal

Assignment Operator

:=

When you are using numeric facts, all variables must be declared as a NUMERIC type.

An example of a numeric fact is, "Day of the Week."



Reference: String Facts

With string facts, you can pass string information to external programs, construct customized messages, write customized external files, and so on. The target variable must be pre-declared as a STRING type. An example of a string fact is, Current Date = "20080709".



Reference: Attribute-Value Facts

An attribute-value association is a statement in which an attribute is described by a particular value. In this way, it resembles a simple fact, except that a simple fact has only one value associated with the attribute, while the attribute-value association uses variables to allow for a variety of values. An example of an attribute-value fact is, "AND TableName IS XXX," since there are several different values that could be substituted for "TableName" after the "IS."



Example: Using BRL

This example of BRL is more complex than the previous one, showing actual syntax for a server for Windows. The BRL shown here allows the DBA to issue a message to end users when specific data sources are inaccessible because of maintenance or system unavailability. It also illustrates how business rules work together with Resource Governor's automatically generated rules.

For this example, the DBA would have to type the list of one or more unavailable data sources into a sequential data set (in this case, C:\temp\unavail.dat). In addition, the DBA would enter a custom message into a file named C:\temp\appname_datasourcename.dat (see the code below for an example). This BRL contains examples of how to detect full-table scans and select *s, two operations that may be expensive to run. It also illustrates how to establish user overrides and Cartesian product join detection, as well as a number of other capabilities.

RULE Conclude DBA Rules Main                                !Rule1 
IF Database Unavailable
OR Nocancel
OR Omnipotent User Override
OR Cartesian Product Join
OR Full Table Scan
OR Selected All Columns
OR Too Many Joins in affect
OR Valid Cancel
THEN DBA Rules Concluded
!
! Check to see if the Database is available 
RULE Check If Database Unavailable                          !Rule 2
IF Initialize and Allocate 
AND Unavailable 
AND CloseFile
AND Lineptr := 1
AND Open custom message file
AND Read and Write Messages
AND Deallocate
THEN Database Unavailable
AND Run := "F"
AND Reason := "DBDown"
! 
! CloseFile
RULE Closefine                                              !Rule3 
THEN CloseFile
AND ACTIVATE %CLOSE
DATA DDN 1
DATA ioresult
!
! Initialize and Allocate 
RULE Initialize and Allocate                                !Rule 4
THEN Initialize and Allocate
AND ioresult := 0
AND DDN 1 := "UNAVAIL" 
AND Dynam Data 1 := " DISK C:\temp\unavail.dat"
AND ACTIVATE %FILEDEF 
DATA DDN 1
DATA Dynam Data 1 
! 
! Unavailable
RULE Unavailable                                            !Rule5
IF Get Table Names
AND Read List
AND ioresult = 0
THEN Unavailable
!
! Build string for FILEDEF
! For example if ibisamp/car is current Table value
! FILEDEF MSGFILE DISK C:\temp\ibisamp_car.dat 
! 
RULE Open custom message file                               !Rule6 
THEN Open custom message file 
AND ioresult := 0 
AND DDN 2 := "MSGFILE" 
AND Dynam Data := " DISK C:\temp\" 
! Need to convert / to _ before FILEDEF of individual message file 
AND Extra String := "/" 
AND ACTIVATE %LOCATE 
DATA Record 
DATA Extra String 
DATA Extra Number 
AND End Pos := Extra Number - 1 
AND Start Pos := 1 
AND ACTIVATE %SUBSTR 
DATA Dynam Data 2 
DATA Record
DATA Start Pos
DATA End Pos
AND Start Pos := Extra Number + 1
AND End Pos := 999
AND ACTIVATE %SUBSTR
DATA Dynam Data 3
DATA Record
DATA Start Pos 
DATA End Pos
AND Extra String := "_"
AND ACTIVATE %CONCAT
DATA Dynam Data 2
DATA Extra String
AND ACTIVATE %CONCAT
DATA Dynam Data 2
DATA Dynam Data 3
AND ACTIVATE %CONCAT
DATA Dynam Data
DATA Dynam Data 2
AND Extra String:= ".dat"
AND ACTIVATE %CONCAT
DATA Dynam Data
DATA Extra String
AND ACTIVATE %FILEDEF
DATA DDN 2 
DATA Dynam Data
!
RULE Read and Write Messages                                !Rule7 
ACTIVATE %GET
DATA DDN 2
DATA Record
DATA ioresult
IF message line 1
AND message line 2
AND message line 3
AND message line 4
AND message line 5
AND ioresult <> 0
THEN Read and Write Messages 
ELSE FORGET message line 1 
AND FORGET message line 2 
AND FORGET message line 3 
AND FORGET message line 4 
AND FORGET message line 5 
AND Lineptr := Lineptr + 1 
AND LOOP
!
RULE to build message line 1                                !Rule8
IF Lineptr = 1
THEN message line 1 
AND Message1 := Record 
ELSE message line 1 
! 
RULE to build message line 2                                !Rule9
IF Lineptr = 2 
THEN message line 2 
AND Message2 := Record 
ELSE message line 2 
! 
RULE to build message line 3                                !Rule 10
IF Lineptr = 3 
THEN message line 3 
AND Message3 := Record 
ELSE message line 3 
! 
RULE to build message line 4                                !Rule 11
IF Lineptr = 4 
THEN message line 4 
AND Message4 := Record 
ELSE message line 4 
!
RULE to build message line 5                                !Rule 12
IF Lineptr = 5 
THEN message line 5 
AND Message5 := Record 
ELSE message line 5 
! 
RULE Read List                                              !Rule 13
ACTIVATE %GET 
DATA DDN 1 
DATA Record 
DATA ioresult 
ACTIVATE %TRIM 
DATA Record 
DATA right 
IF Database Found 
OR ioresult <> 0 
THEN Read List 
ELSE FORGET Database Found 
AND LOOP 
! 
RULE Find Unavailable Database                              !Rule 14
IF Tablename 1 = Record 
OR Tablename 2 = Record 
OR Tablename 3 = Record 
OR Tablename 4 = Record 
OR Tablename 5 = Record 
THEN Database Found 
!
RULE Get Table Names                                        !Rule 15 
ACTIVATE %TABLES 
DATA Table IS WHAT 
IF Make string 1 
OR Make string 2 
OR Make string 3 
THEN Get Table Names 
! 
RULE Make string 1                                          !Rule 16
IF Table IS ibisamp/employee 
THEN Make string 1 
AND Tablename 1 := "ibisamp/employee" 
! 
RULE Make string 2                                          !Rule 17
IF Table IS ibisamp/car 
THEN Make string 2 
AND Tablename 2 := "ibisamp/car" 
! 
RULE Make string 3                                          !Rule 18
IF Table IS ibisamp/jobfile
THEN Make string 3
AND Tablename 3 := "ibisamp/jobfile"
! 
! Deallocate the DDNAME for unavail list and the message file
RULE Deallocate                                            !Rule 19
THEN Deallocate
AND Dynam Data := "CLEAR"
AND Dynam Data 1 := DDN 1 
AND ACTIVATE %FILEDEF 
DATA Dynam Data 1 
DATA Dynam Data 
AND Dynam Data 1 := DDN 2 
AND ACTIVATE %FILEDEF 
DATA Dynam Data 1 
DATA Dynam Data 
! 
RULE Valid Cancel                                           !Rule 20
IF Run <> "T"
THEN Valid Cancel 
AND Message1 := "Execution cannot be allowed." 
AND Message2 := "Query may potentially consume excessive resources." 
!
! Disable governing between 6 PM and 8 AM 
RULE Nocancel                                               !Rule 21
IF Run <> "T"
AND Hours and minutes >= "1800" 
AND Hours and minutes <= "0800" 
THEN Nocancel 
AND Run := "T" 
!
RULE To allow Omnipotent users                              !Rule 22
IF Userid = "CFO " 
OR Userid = "PRESIDNT" 
OR Userid = "VICEPRES" 
THEN Omnipotent User Override 
AND Run := "T" 
! 
! No WHERE conditions so a FULL table scan is imminent
RULE No Where Conditions Found                              !Rule 23
IF Number of relations = 0 
THEN Full Table Scan 
AND Run := "F" 
AND Reason := "FullScan" 
AND Message1 := "Please add a WHERE condition to your request" 
AND Message2 := "to avoid a full table scan and excessive" 
AND Message3 := "resource consumption." 
! 
RULE Selected All Columns                                   !Rule 24
IF Select asterisk
THEN Selected All Columns 
AND Run := "F" 
AND Reason := "SELECT*" 
AND Message1 := "Please do not choose EVERY columns in this" 
AND Message2 := "particularly wide database.Instead" 
AND Message3 := "choose specific column names in your query." 
!
! Check to see if the user is issuing a Cartesian Product with
! the join of two databases 
RULE Cartesian Product Join                                 !Rule 25
IF Number of tables = 2 
AND Number of relations = 0 
THEN Cartesian Product Join 
AND Run := "F" 
AND Reason := "CartProd" 
AND Message1 := "Apply a WHERE clause to constrain this" 
AND Message2 := "join. Your request is a Cartesian Product" 
AND Message3 := "and will return n X m rows. " 
! 
! The use of many joins can use excessive resources
RULE Too Many Joins in affect                               !Rule 26
IF Number of tables >= 4
THEN Too Many Joins in affect 
AND Run := "F" 
AND Reason := ">=4joins" 
AND Message1 := "You are using too many joined data objects." 
AND Message2 := "For efficiency, extract data to temp files" 
AND Message3 := "and join the temp files to persistent databases." 
! 
RULE Conclude DBA Rules Fallthrough Rule
THEN DBA Rules Concluded

Rule 1 is the main rule and is inspected first. If any one of the OR conditions fires (that is, is true), then the rule is completed and no further OR conditions of Rule 1 are inspected. The next rule, Rule 2, must fire five additional rules in order to confirm that a database is unavailable, as well as to send a message to the end user. You can follow the rules to their conclusion by reading Rules 3 through 7. Rule 7 reads a flat file and sets up messaging output (Rules 8 through 12). Rule 13 reads the list of currently unavailable databases from a flat file. Rule 20 messages the user when the Resource Governor has determined using Resource Governor's automatically generated rules that the query exceeds the user input threshold. Rules 21 through 26 address specific query characteristics. For example, Rule 25 checks to see if the request is joining two tables without an IF/WHERE condition (a Cartesian product join), which is a potentially costly and inefficient action. Rule 19 performs clean-up operations.


WebFOCUS