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
|
|
A simple fact is a straightforward expression that requires only a true or false answer. An example of a simple fact is "Select asterisk."
|
|
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."
|
|
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".
|
|
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."
|
|
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 |