Customized Rule Examples

The following examples suggest more uses for business rules. The goal of every custom rule group is DBA Rules Concluded. Alternate goal variables may be used as long as they are tested in subsequent rules and DBA Rules Concluded is ultimately satisfied.

Example: Initialize All of the ATTRIBUTE Variables

This example illustrates the custom rule that would make additional information available to the rule file. All of the functions called with the ACTIVATE keyword will initialize the ATTRIBUTE variables listed with the DATA statements. This allows you to write additional custom rules. 

! Process internal all attribute/value functions
RULE Get information about the query
ACTIVATE %TABLES
DATA TableName IS WHAT
ACTIVATE %COLUMNS
DATA Column name IS WHAT
ACTIVATE %DISTINCT
DATA Distinct columns
ACTIVATE %LEFTREL
DATA Left relational column IS WHAT
ACTIVATE %RIGHTREL
DATA Right relational column IS WHAT
ACTIVATE %RELOPCOL
DATA Relational IS WHAT
ACTIVATE %GROUPBY
DATA Group by column IS WHAT
ACTIVATE %ORDERBY
DATA Order by column IS WHAT
ACTIVATE %FUNCTION
DATA Function name IS WHAT
ACTIVATE %FUNCOLS
DATA Function column IS WHAT
ACTIVATE %RELOPS
DATA Relational operator IS WHAT
ACTIVATE %RELATION
DATA Relational statement IS WHAT
ACTIVATE %RELTABS
DATA Table relations ARE WHAT
ACTIVATE %ORNOTERR
DATA Bad or not condition
THEN All functions processed

Example: Checking for Table Names Used

This rule example illustrates how to check for one or more table names used in the original SQL or TABLE request. For example, this rule file might have been built for TABLE2, and you know you never want TABLE1 used with TABLE2. 

RULE Check table name
IF All functions processed
AND TableName IS TABLE2      ! This rule file is for TABLE2
AND TableName IS TABLE1      ! TABLE1 cannot be used with TABLE2
THEN DBA Rules Concluded
AND Run := "F"
AND Reason := "TABLE"
AND Rule Number := 9999
AND Message1 := "You cannot use TABLE1 and TABLE2 in the same request."

Example: Making Sure a Request Runs If an Equality Condition Exists

This rule example assures that if an equality condition exists on an indexed column in a WHERE clause, the request runs.

RULE Check Relation
IF All functions processed
AND Relational IS TABLE1.COLUMN1.EQ.LITERAL
THEN DBA Rules Concluded
AND Run := "T"
AND Reason := "INDEX"

Example: Canceling Requests That Use SELECT * and Have No WHERE Clauses

This rule example cancels all requests that use a SELECT * and lack WHERE clauses. It also sets the Rule Number and Reason, which are saved in the SMGOVEND RG repository table and available for reporting. The Rule Number and Reason also display to the user in a cancel message. Message1 through Message5 may contain message information that is displayed with an advise or cancel message to the user and the edaprint.log file. 

RULE Check for Asterisk
IF Select asterisk
AND Number of Relations = 0
THEN DBA Rules Concluded
AND Run := "F"
AND Reason := "SELECT*"
AND Rule Number := 9998
AND Message1 := "Please add a WHERE clause to your SELECT statement."
AND Message2 := "You selected every row in the table."
AND Message3 :=:"5 Message variables are available."

Example: Making Sure No Requests Are Canceled

The following rule example ensures no requests are canceled.

RULE Do not cancel any queries
THEN DBA Rules Concluded
AND Run := "T"

Example: Using a Wild Card for Column Names

These rules catch any use of HEIGHT, WEIGHT, COUNTRY or CAR from the CAR file and allow the request to run. If they are not used, the process will drop down to the second rule and cancel. 

RULE Check for column group
IF All functions processed
AND Column name IS $IGHT
OR Column name IS C$ 
THEN DBA Rules Concluded
AND Run := "T"
RULE fall through
THEN DBA Rules Concluded
AND Run := "F"

Example: Exclude Ad Hoc Requests

This rule checks if the procedure name is blank, which indicates an ad hoc request.

RULE No adhoc
IF Procedure = " "
THEN DBA Rules Concluded
AND Run := "F"

Example: No Temporary Computes or Defines Allowed on Relational Data

This rule checks for any temporary columns. They are defined or computed at run time, not in the Master File definition. This also illustrates how to check for a relational data source. You do not need to use an ACTIVATE function to get the value of the temporary field or relational source because they are facts that are passed to the governor when the rules are run. 

RULE check temporary fields 
IF Temporary field 
AND Relational source 
THEN DBA Rules Concluded
AND Rule Number := 1
AND Run := "F" AND
Reason := "temp"
AND Message1 := "Cannot use a a temporary field when data source is relational"

Example: Introducing a Driver Rule

This example has two independent rules, each of which could return true. The first one that does will conclude the rule file. If the first rule does not return true, then the governor will proceed to the next rule. Multiple rules within a rule file can be linked together with a driver rule using AND/OR so that they are applied together. 

!
! Rule Wizard Generated Template
!
RULE Select Asterisk
IF Select asterisk
THEN DBA Rules Concluded
AND Rule Number := 1
AND Run := "F"
AND Reason := "Select *"
AND Message1 := "No select asterisk."
!
RULE Allow Joins
IF Number of tables > 3
THEN DBA Rules Concluded
AND Rule Number := 2
AND Run := "F"
AND Reason := "Joins"
AND Message1 := "Only 3 joins allowed."
!

To combine the rules so that both have to be true for a request to be governed, a driver rule is needed that calls the existing two rules and specifies as its conclusion the same conclusion as before. The driver rule is: 

RULE Stop request if Select Asterisk with more than 3 joins
IF Have Select Asterisk
AND Too Many Joins
THEN DBA Rules Concluded
AND Rule Number := 1
AND Run := "F"
AND Reason := "BUSINESS"
AND Message1 := "No select asterisk allowed with more than 3 joins."
ELSE DBA Rules Concluded
AND Rule Number := 1
AND Run := "T"

The two existing rules need to be changed as follows:

RULE Check for Select Asterisk
IF Select asterisk
THEN Have Select Asterisk
!
RULE Check Join number
IF Number of tables > 3
THEN Too Many Joins
!

Notice that the conclusion for the existing rules has changed, but the conditions for each remain the same.

Example: Creating a Rule File to Follow Specific Rules

The following example will create a rule file that will apply the following rules to a query:

The complete rule file to accomplish this is:

RULE Global Rule One
IF User is the Boss
OR Disallow Request at this time
THEN DBA Rules Concluded  
!
RULE Check User ID
Start Pos := 1
End Pos := 4
ACTIVATE %SUBSTR
DATA Extra String
DATA Userid
DATA Start Pos
DATA End Pos
ACTIVATE %UPCASE
DATA Extra String
IF Extra String = "BOSS"
THEN User is the Boss
AND Run := "T"
!
RULE Check TABLE name and time of day
IF Disallow TABLE Name
AND This is Morning
THEN Disallow Request at this time
AND Run := "F"
AND Reason := "GlobalR1"
AND Message1 := "Access to any data source starting with ca from ANY "
AND Message2 := "application directory from 9AM to NOON is restricted"
ELSE Run := "T"
!
RULE Check TABLE Name
ACTIVATE %TABLES
DATA Table IS WHAT
IF Table IS $/ca$
THEN Disallow TABLE Name
!
RULE Check time of day
IF Hours and minutes >= "0900"
AND Hours and minutes <= "1200"
THEN This is Morning
!
RULE fall through
THEN DBA Rules Concluded
Note:
  • Since the BOSS user IDs can run at any time, it is logical to test for this first. The rule uses two built-in functions:
    • %SUBSTR to get the first four characters of the supplied string variable Userid.
    • %UPCASE the output of %SUBSTR.
  • The next rule, which is OR with the first, acts like a driver as it combines two rules using AND.
    • Does the current (any) table name active in the request start with ca?
    • If so, is the time between 9 A.M. and noon?
    • If the answer is Yes to both, then this OR rule will disallow the request at this time and the conclusion that is coded in this rule will be used (AND Run := "F").
WebFOCUS