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.
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
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."
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"
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."
The following rule example ensures no requests are canceled.
RULE Do not cancel any queries THEN DBA Rules Concluded AND Run := "T"
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"
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"
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"
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.
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
WebFOCUS | |
Feedback |