BRL is capable of representing the following types of information within a single rule file or rule:
These facts are described in the following sections. These sections give examples based on the following rule:
RULE Storm Conditions
IF There are high winds present
AND Barometric pressure < 28.8
AND The kind of clouds present are cumulus
AND month = "March"
OR month = "April"
THEN There is a storm present
How to: Example: Reference: Activate Functions |
A simple fact is a straightforward expression that requires only a true or false answer. An example of a simple fact is, "There are high winds present."
Numeric data allows you to 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 |
Assignment Operator |
:= |
When you are using numeric facts, all variables must be declared as a NUMERIC type. An example of a numeric fact is, "Barometric pressure < 28.8."
String data allows you to 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, Extra String = "March".
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, "The kind of clouds present ARE cumulus," since there are several different values that could be substituted for "cumulus" after the "ARE."
The following BRL example shows actual syntax for a server for OS/390 and z/OS. The BRL shown here allows the DBA to issue a message to end-users when specific databases are inaccessible because of maintenance or system unavailability. It also illustrates how custom rules work together with SmartMode's automatically generated rules and the concept of back-chaining. You see this concept put into practice with the first rule checking many conditions that are decided in subsequent rules. This example uses GKECR, the BRL supplied in the default custom rule file installed with the product.
For this example, the DBA would have to type the list of one or more unavailable databases into a sequential data set (in this case, SYS1.UNAVAIL.DBASES.LIST). In addition, the DBA would enter a custom message into the data set SYS1.MESSGE.databasename. 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 !Rule 1
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
!
RULE Check If Database Unavailable !Rule 2
IF Initialize and Allocate
AND Unavailable
AND Open custom message file
AND Read and Write Messages
AND Deallocate
THEN Database Unavailable
AND Run := "F"
AND Reason := "DB Down"
!
RULE Initialize and Allocate !Rule 3
THEN Initialize and Allocate
AND ioresult := 0
AND DDN 1 := "UNAVAIL"
AND Dynam Data := "ALLOC FILE UNAVAIL"
AND Dynam Data 1 := " SHR REUSE DS PGMEIB.UNAVAIL.DBASES.LIST"
AND ACTIVATE %DYNAM
DATA Dynam Data
DATA Dynam Data 1
!
RULE Unavailable !Rule 4
IF Get Table Names
AND Read List
AND ioresult = 0
THEN Unavailable
!
RULE Find Unavailable Database !Rule 5
IF Tablename 1 = Record
OR Tablename 2 = Record
OR Tablename 3 = Record
OR Tablename 4 = Record
OR Tablename 5 = Record
THEN Database Found
!
RULE Open custom message file !Rule 6
THEN Open custom message file
AND DDN 2 := Record
AND Dynam Data := "ALLOC FILE "
AND ACTIVATE %CONCAT
DATA Dynam Data
DATA DDN 2
AND Dynam Data 1 := " SHR REUSE DS PGMEIB.MESSAGE."
AND ACTIVATE %CONCAT
DATA Dynam Data 1
DATA DDN 2
AND ACTIVATE %DYNAM
DATA Dynam Data
DATA Dynam Data 1
!
RULE Read and Write Messages !Rule 7
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 !Rule 8
IF Lineptr = 1
THEN message line 1
AND Message1 := Record
ELSE message line 1
!
RULE to build message line 2 !Rule 9
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 Get Table Names !Rule 14
ACTIVATE %TABLES
READ
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 15
IF Table IS CAR
THEN Make string 1
AND Tablename 1 := "CAR"
!
RULE Make string 2 !Rule 16
IF Table IS PGMEIB.COMPANY
THEN Make string 2
AND Tablename 2 := "COMPANY"
!
RULE Make string 3 !Rule 17
IF Table IS PGMEIB.EMPLOYEES
THEN Make string 3
AND Tablename 3 := "EMPLOYEES"
!
RULE Valid Cancel !Rule 18
IF Run <> "T"
THEN Valid Cancel
AND Message1 := "Execution cannot be allowed as query"
AND Message2 := "will potentially consume excessive resources."
!
! rule for Disable governing after 6 PM and before 8 AM
RULE Nocancel !Rule 19
IF Run <> "T
AND Hours and minutes >= "1800"
AND Hours and minutes <= "0800"
THEN Nocancel
AND Run := "T"
!
RULE To allow Omnipotent users !Rule 20
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 21
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 22
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 23
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 be a resource hog
!
RULE Too Many Joins in affect !Rule 24
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."
!
! Deallocate the DDNAME for unavail list and the message file
RULE Deallocate !Rule 25
THEN Deallocate
AND Dynam Data := "FREE FILE"
AND Dynam Data 1 := DDN 1
AND ACTIVATE %DYNAM
DATA Dynam Data
DATA Dynam Data 1
AND Dynam Data 2 := DDN 2
AND ACTIVATE %DYNAM
DATA Dynam Data
DATA Dynam Data 2
!
RULE Conclude DBA Rules Fallthrough Rule
THEN DBA Rules Concluded
The following steps outline the backward chaining process for this BRL:
You can add customized rules as required by your site. All BRL keywords must be written in uppercase.
Variable Declaration:
Keyword |
Description |
ATTRIBUTE |
Declares the type of a variable to be an attribute-value pair. Each pair is treated as a simple Boolean. There are two types of attributes: single-valued (only one attribute-value pair can be true at any moment) and multiple-valued (one, some, or all the attribute-value pairs can be true simultaneously). The default is single-valued. (See MULTI in the Control Element Selectors table.) |
NUMERIC |
Declares the type of a variable to be numeric. |
SIMPLEFACT |
Declares the type of a variable to be logical, that is, true or false. |
STRING |
Declares the type of a variable to be a character string. |
Control Element Selectors:
Keyword |
Description |
EXHAUSTIVE |
Normally, once a SIMPLEFACT is determined to be true or false, no other attempts to determine its value will be performed and no other rules that conclude it will be pursued. If EXHAUSTIVE is specified for the SIMPLEFACT, all rules that conclude it will be pursued even if its value has already been determined. |
MULTI |
Defines an ATTRIBUTE as possibly having multiple values. |
Rule Declaration:
Keyword |
Description |
ACTIVATE |
Allows execution of external routines or pre-defined internal functions. For more information see Activate Functions. |
AND |
Serves as a logical operator for joining variables or a sequencing operator when it connects a series of actions or statements. In premises containing both AND and OR, statements are evaluated from the top to the bottom of the rule, in order of appearance. |
ARE |
The keywords IS, ARE, and the symbol \ can be used interchangeably. (See the keyword IS.) |
DATA |
When used with READ, causes a variable to be assigned a value that is read from memory in the server. In conjunction with WRITE, DATA causes the value of a fact to be written to memory in the server. The purpose of DATA is to transfer the value of a variable between a rule file and the server or functions invoked through the ACTIVATE keyword. Each DATA keyword must be on its own line in the rule file. The value of a variable read in by DATA goes into the session context and is useable from then on. |
END |
Marks the end of a rule file and signals the termination of the text of BRL. Omitting END will result in a compiler error. |
IF |
Introduces the premise of a rule. There can only be one IF per rule. Multiple premises can be tested and joined by AND or OR. |
INIT |
Enters values into the session context when a rule file is first executed. The purpose of INIT is to assign values needed when processing begins. Variables initialized with INIT are available before any rules execute. |
IS |
Binds an ATTRIBUTE variable to a value. In the premise of a rule, the IS value is interpreted as a test to determine whether the attribute-value pair is a true statement. In a conclusion, the IS value assigns a value of true to the statement. The keywords IS, ARE, and the symbol \ can be used interchangeably. |
NOT |
Negates the true value of a variable. In the premise of a rule, a statement beginning with NOT is true if the variable following NOT is false and vice versa. In the conclusion of a rule, NOT enters the following variable into the session with the value false if the rule executes. |
OR |
The logical operator for connecting variables in the premise of a rule. OR makes it possible to combine rules having the same conclusion into a single rule. In premises containing both AND and OR, statements are evaluated from the top to the bottom of the rule, in order of appearance. |
READ |
Causes values from the following DATA keywords to be assigned to the variables listed with DATA. |
RULE |
Specifies the name of a BRL rule. Every rule must begin with the reserved word RULE followed by the name of the rule. Rule names do not have to be distinct. All rules must contain the keyword THEN followed by a variable that is a SIMPLEFACT or ATTRIBUTE. |
THEN |
Introduces the primary conclusion of a rule. Every rule must have the keyword THEN. All the BRL statements in the primary conclusion are executed when the premise of the rule is true. The first statement following THEN must be a SIMPLEFACT or ATTRIBUTE type of variable. Numeric, string, and procedural statements are not permitted immediately following THEN although they are allowed elsewhere in the conclusion. |
TITLE |
Marks the beginning of a BRL rule file. The first line of the rule file is the TITLE line. The length of the title cannot exceed 60 characters. |
WHAT |
Used with IS, ARE, or the symbol \ and the keyword DATA to indicate that multiple values are to be returned or sent for an ATTRIBUTE (as in DATA attribute IS WHAT). |
WRITE |
Causes values from the following DATA keywords to be passed back to the server or ACTIVATE function. A WRITE keyword must be followed by one or more DATA keywords. |
$ [file] |
The include operator, $, specifies a file to be included in a rule file. The name following the $ symbol will be merged into the rule file at compile time. Files included with the $ operator cannot contain other include files. The $ operator must be located in column one. |
! |
The comment operator marks the beginning of a comment in a BRL source file. |
" " |
Quotation marks designate a string literal. Literals must be enclosed in double quotation marks to permit the use of reserved BRL symbols and words and leading and trailing blanks when assigning a value to a variable, and to differentiate a literal value for a variable from a variable name. |
: |
The continuation operator indicates that a line in a BRL source file is continued on the next line. |
:= |
The assignment operator assigns the value on the right side of the := symbol to the variable on the left side (this operator is used with NUMERIC and STRING variables). |
\ |
The keywords IS, ARE, and the symbol \ can be used interchangeably. (See the keyword IS.) |
Arithmetic Operators |
Used in performing calculations involving numeric expressions. ( ) Parenthesis. * Multiplication. / Division. + Addition. - Subtraction. |
Relational Operators |
Relational operators are not allowed in conclusions of rules. Strings or numerics can be compared. = Equal to. < > Not equal to. < Less than. > Greater than. <= Less than or equal to. >= Greater than or equal to. |
Variables are case and white-space sensitive. For example, TIME is a different variable than time. The following sections list all the variables provided in a rule file. A MULTI can have values that range from 1 to 256.
These rules apply to all of the variables listed.
In an assignment, a premise, or a conclusion in a rule, variables are always referenced by themselves or with the NOT operator. For example, IF simple1 or IF NOT simple1.
NUMERIC:
Variable |
Description |
Current Time Threshold |
The threshold in effect for elapsed or CPU time in seconds for the current date and time. |
Current Rows Threshold |
The threshold in effect for the maximum size of the requested result set. |
Current IOs Threshold |
The threshold in effect for the maximum input/output operations used to satisfy the request. |
Time Threshold |
The threshold estimated by the rule file for elapsed or CPU seconds. |
Rows Threshold |
The threshold estimated by the rule file for maximum size of result set. |
IOs Threshold |
The threshold estimated by the rule file for the maximum number of input/output operations potentially used in the current request. |
Rule Number |
An arbitrary number assigned to a rule to enable the reference of what rule did or did not cancel a request. |
Day of Week |
The day of the week indicated as 0 - 6, where 0 is Sunday and 6 is Saturday. |
Record Limit |
The value of the FOCUS keyword RECORDLIMIT set in the FOCUS session or the request. |
Extra Number |
Defined for use by custom rules. |
Number of unions |
The number of UNION statements contained in the request. |
Number of union alls |
The number of UNION ALL statements contained in the request. |
Number of tables |
The number of tables or data sources contained in the request. |
Number of relations |
The number of relational clauses contained in the request. |
Number of group bys |
The number of GROUP BY columns contained in the request. |
Number of order bys |
The number of ORDER BY columns contained in the request. |
Number of functions |
The number of functions contained in the request. |
Number of columns |
The number of columns contained in the request or records inserted into the table SMCOLUMNS. It is primarily the number of columns selected except when an asterisk is used. In that case, the number of columns is one. |
ioresult |
Defined for use in custom rules. |
Lineptr |
Defined for use in custom rules. |
SIMPLEFACT:
Variable |
Description |
Correlated query |
Used by the automated rules created with GKERULE, and can be used in custom rules as well. |
Select asterisk |
Used by the automated rules created with GKERULE, and can be used in custom rules as well. |
Bad or not condition |
Used with the %ORNOTERR function. Identifies whether an invalid logical combination of not/or conditions are specified in the current SQL WHERE clause. |
Distinct columns |
Used with the %DISTINCT function. Specifies whether the SQL DISTINCT parameter was used in the current query. |
message line 1 |
Defined for use in custom rules. |
message line 2 |
Defined for use in custom rules. |
message line 3 |
Defined for use in custom rules. |
message line 4 |
Defined for use in custom rules. |
message line 5 |
Defined for use in custom rules. |
STRING:
Variable |
Description |
Userid |
The ID of the client logged in. |
Reason |
An eight-character string that contains a reason why the Governor decided what it did. This value is stored in the Usage Monitor table, SMGOVERN. It is freely available to be modified in custom rules. |
Current Date |
The current date in the form YYYYMMDD. |
Hours and minutes |
The current time in the form HHMM. |
Message1 |
The first string defined that can contain a custom message. It is always returned to the server. It will be used if there is a value assigned to it and will override the standard cancellation message. |
Message2 |
The second string defined that can contain a custom message. It is always returned to the server. It will be used if there is a value assigned to it and Message1. |
Message3 |
The third string defined that can contain a custom message. It is always returned to the server. It will be used if there is a value assigned to it and the preceding message strings. |
Message4 |
The fourth string defined that can contain a custom message. It is always returned to the server. It will be used if there is a value assigned to it and the preceding message strings. |
Message5 |
The fifth string defined that can contain a custom message. It is always returned to the server. It will be used if there is a value assigned to it and the preceding message strings. |
Format |
A string value unused at this time. Available for any use in custom rules. |
Extra String |
A string value unused at this time. Available for any use in custom rules. |
Run |
The indicator for whether the decision is to run or cancel the request. It is initialized to T. Custom rules can set it to F if desired, which will cause the request to cancel. |
Shift |
The name of the shift in effect at the current time. Otherwise, this variable may be used for something else in custom rules. |
Request Type |
A string value containing the active request type. For example, T (Table), S (Select), etc. |
Procedure |
The active focexec name executing. |
DDN 1 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
DDN 2 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
DDN 3 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
DDN 4 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
DDN 5 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Tablename 1 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Tablename 2 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Tablename 3 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Tablename 4 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Tablename 5 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Dynam data |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Dynam data 1 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Dynam data 2 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Dynam data 3 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Dynam data 4 |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Record |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
right |
A string value unused at this time. Example of use in custom rules provided in GKECR. |
Connection Address |
Unused and available for custom rules. |
HOLD Format |
The format of the hold file specified in the current request, if there is one. |
ATTRIBUTE:
Variable |
Description |
Table Name |
Used by the automated rules created with GKERULE. |
Relation |
Used by the automated rules created with GKERULE. |
Table |
Used with the %TABLES function. Its values are the table names specified in the SQL FROM clause of the current query. |
Column Name |
Used with the %COLUMNS function. Its values are the column names used in the SQL SELECT clause of the query. |
Relational |
Used with the %RELOPCOL function. Its values are the relational statements from the SQL WHERE clause with the right-hand side specified as LITERAL or FIELD. For example, WHERE (SMQUERY.SMKEY = SMFROMS.SMKEY) AND (SMQUERY.SMDATE > '19960101') would become the values: SMQUERY.SMKEY.EQ.FIELD and SMQUERY.SMDATE.GT.LITERAL |
Left relational column |
Used with the %LEFTREL function. Its values are the left-hand side of the relational statements specified in the SQL WHERE clause. Using the previous example, the values would be: SMQUERY.SMKEY and SMQUERY.SMDATE |
Right relational column |
Used with the %RIGHTREL function. Its values are the right-hand side of the relational statements specified in the SQL WHERE clause when a field is specified. Using the previous example, the only value used would be: SMQUERY.SMKEY |
Group by column |
Used with the %GROUPBY function. Its values are the column names specified in the SQL GROUP BY clause of the current query. |
Order by column |
Used with the %ORDERBY function. Its values are the column names specified in the SQL ORDER BY clause of the current query. |
Function |
Used with the %FUNCTION function. Its values are the function names specified in the SQL WHERE clause of the current query. |
Function Column |
Used with the %FUNCOLS function. Its values are the function and column names specified in the SQL WHERE clause of the current query. |
Relational Operator |
Used with the %RELOPS function. Its values are the relational operators specified in the SQL WHERE clause. Using the previous example, the values EQ.FIELD and GT.LITERAL would be used. |
Relational Statement |
Used with the %RELATION function. Its values are the entire relational statements from the SQL WHERE clause. Using the previous example, the values used would be SMQUERY.SMKEY.EQ.creator.SMFROMS.SMKEY and SMQUERY.SMDATA.GT.19960101 |
Table Relations |
Used with the %RELTABS function. Its values are the table names relation from the SQL WHERE clause. Using the previous example, the value used would be: SMQUERY.EQ.creator.SMFROMS |
SmartMode provides functions that are designed to assist with application development using Custom rules. These include String Functions, Date Functions, File Functions, and Current Request Functions. These functions are described in the following sections, along with variables and syntax examples.
Note: All functions begin with a percent (%) sign and must be entered in uppercase.
The ACTIVATE command can be executed directly from custom rules. The command syntax is
ACTIVATE program_name
where:
Parameters passed to and received from programs are identified through the DATA statement(s) as shown in the following example,
ACTIVATE pgmname
DATA parm 1
DATA parm 2
.
.
.
DATA parm n
where:
Note: The ACTIVATE and DATA keywords must always be entered in uppercase.
These functions are provided to modify and control text for items such as custom messages and the construction of JCL.
Function |
Description |
Syntax |
%CONCAT |
Concatenates multiple strings together. The syntax example concatenates the contents of Input String 1, Input String 2, ...Input String n to the contents of InputOutput String. |
ACTIVATE %CONCAT |
%DELETE |
Deletes characters between start and end columns inclusively. The syntax example deletes all characters in InputOutput String between the specified Start position and End position, inclusively. |
ACTIVATE %DELETE |
%FTOA |
Converts a NUMERIC value to a STRING value. The syntax example converts the numeric value of Input Numeric to a string value with a format defined by Format String and places the resulting string into Output String. Format String is in the form n.d, where n represents the total number of characters (including decimal point and sign) of the output string, and d represents the number of decimal places. A value of n=0 indicates that the number of places to the left of the decimal point will be as large as necessary to hold the entire number, but no larger than needed. |
ACTIVATE %FTOA |
%INSERT |
Inserts one string into another at a specified location. The syntax example inserts the characters in Input String into InputOutput String at the specified Start position. |
ACTIVATE %INSERT |
%LENGTH |
Returns the number of characters in a string. The syntax example counts the number of characters in Input String and puts it in Length. |
ACTIVATE %LENGTH |
%LOCATE |
Returns the start position of specified text in a given string. The syntax example locates the position of Find String in Search String and places the result in Position. |
ACTIVATE %LOCATE |
%LOWCASE |
Converts a string to lowercase. The syntax example converts InputOutput String to lowercase. |
ACTIVATE %LOWCASE |
%OVERLAY |
Overlays one string with another at a specified position. The syntax example overlays InputOutput String with Input String at position Numeric Position for the length of Input String. The starting position is one relative, for example, the first position in the string that can be overlaid is position =1. |
ACTIVATE %OVERLAY |
%PAD |
Pads a given string with a given character to a specified length. The syntax example pads InputOutput String with the character in Pad Character for a total of Length characters. The value of L_B_R is: L if the pad characters are inserted to the left of the string. R if the pad characters are inserted to the right of the string. B if the string is to be centered between the pad characters. |
ACTIVATE %PAD |
%SUBSTR |
Extracts substrings. The syntax example extracts a substring of Input String, which contains the characters between Start Character and End Character, inclusive, and places the resulting string in Output String. |
ACTIVATE %SUBSTR |
%TRIM |
Removes blanks from the left, right, or both sides of a string. The syntax example trims blanks from InputOutput String. The value of L_B_R is: L if blanks are to be trimmed from the left side only. R if blanks are to be trimmed from the right side only. B if blanks are to be trimmed from both sides. |
ACTIVATE %TRIM |
%TRUNC |
Truncates a string to a specified length. The syntax example truncates InputOutput String to Length characters. Truncation always occurs on the right side of the string. |
ACTIVATE %TRUNC |
%UPCASE |
Converts string to uppercase. The syntax example converts InputOutput String to uppercase. |
ACTIVATE %UPCASE |
The following table lists and describes functions that modify and control how date and time data types are passed.
Function |
Description |
Syntax |
%DATE |
Returns query date with slashes. Given the month, day, and year (numeric values), the syntax example updates Query Date with the formatted string mm/dd/yyyy, dd/mm/yyyy, yyyy/mm/dd, and so on. The order of the DATA statements can be changed if a different date format is required. For example, if the required format was dd/mm/yyyy, then day would be on the first DATA statement and month on the second. |
ACTIVATE %DATE |
%DOW |
Given a numeric day of the week, returns the name of the day. The syntax example converts the numeric value in day of week to the corresponding character name and places it in dow. The key words short and upper are optional and can be passed to the %DOW function. If short is passed, then only the three character day of the week name will be used. If upper is passed, then the string for dow will be in uppercase. The order in which these optional parameters are passed to the function is not important. |
ACTIVATE %DOW |
%MONTH |
Given a numeric month, returns the name of the month. The syntax example converts the numeric value in month to the corresponding character value in mon name. The key words short and upper are optional and can be passed to the %MONTH function. If short is passed, then only the three character month name will be used. If upper is passed, then the string for mon will be in uppercase. The order in which these optional parameters are passed to the function is not important. |
ACTIVATE %MONTH |
%TIME |
Returns query time with colons. Given the Hour and Minute (numeric values), the syntax example updates Query Time with the formatted string hh:mm. |
ACTIVATE %TIME |
The following table lists and describes file functions that access and update external data files.
Function |
Description |
Syntax |
%CLOSE |
Closes a dataset or file opened by %GET or %PUT. The syntax example closes the sequential file allocated to ddname. If member name is specified, it closes the specified member of the PDS allocated to ddname. The value of ioresult is zero if the close is successful. It will have a value of 28 if the ddname is not allocated. |
ACTIVATE %CLOSE (member name is optional) |
%COPY |
Copies the contents of the dataset allocated to SYSUT1 to the dataset allocated to SYSUT2. The syntax example copies data from the sequential file allocated to ddname SYSUT1 into the sequential file allocated to the ddname SYSUT2. The value of ioresult will be zero if the copy is successful. It will have a value of 28 if SYSUT1 is not allocated or a value of 29 if SYSUT2 is not allocated. |
ACTIVATE %COPY |
%DYNAM |
Allocates and frees datasets. The syntax example dynamically allocates, concatenates, or frees a dataset to or from a specified ddname. The syntax is the same as the FOCUS or EDA/SQL Dialogue Manager DYNAM command. DYNAM SUBMIT is not supported. The command can be continued in several different strings. |
ACTIVATE %DYNAM |
%GET |
Reads a record. The syntax example reads from the sequential file allocated to DDN 1 and puts the data in Record. If Extra String is specified, it reads the record from the specified member of the PDS contained in Extra String allocated to DDN 1. The value of ioresult is zero if the read is successful. It will have a value of 1 if the dataset is at end of file or a value of 28 if the ddname is not allocated. |
ACTIVATE %GET |
%PUT |
Writes a record. The syntax example writes the contents of record to the sequential file allocated to DDN 1. If Extra String is specified, it writes the record to the specified member of the PDS contained in Extra String allocated to DDN 1. The value of ioresult is zero if the write is successful. It will have a value of 28 if the DDN 1 is not allocated. |
ACTIVATE %PUT |
%SUBMIT |
Submits a JCL jobstream for execution. The syntax example submits the sequential dataset allocated to the ddname in DDN 1 for MVS JOB execution. |
ACTIVATE %SUBMIT |
The following table lists and describes internal functions that are executable as ACTIVATE routines in the rules.
Function |
Description |
%COLUMNS |
Returns column names. |
%DISTINCT |
Determines if DISTINCT columns were requested. |
%FUNCOLS |
Returns functions with column names. |
%FUNCTION |
Returns function names. |
%GROUPBY |
Returns group names. |
%LEFTREL |
Returns relational columns (left side). |
%ORDERBY |
Returns ordered columns. |
%ORNOTERR |
Invalid combination of OR and NOT logic. |
%RELATION |
Returns the relational statements. |
%RELOPCOL |
Returns the relational column/operator combinations and determines whether they compare against a literal or a field. |
%RELOPS |
Returns relational operator AND/OR with LITERAL or FIELD. |
%RELTABS |
Returns relational conditions between tables. |
%RIGHTREL |
Returns relational columns (right side). |
%TABLES |
Returns table names. |
%SAVEQRY |
Saves the current translated SQL request to a dataset or file. In the syntax example, if the original form of the query was SQL, the translated query will be copied to the sequential dataset allocated to the ddname in DDN 1. For translated SQL, the value of Procedure must be "FOCSQLXX." |
The following examples are based on this sample SQL request:
SELECT T1.COLUMN1,T2.COLUMN2,AVG(T2.COLUMN4),MAX(T2.COLUMN5)
FROM CREATOR.TABLE1 T1,CREATOR.TABLE2 T2
WHERE (T1.INDEX1 = T2.INDEX2) AND (T1.COLUMN3 > '19951231')
GROUP BY T1.COLUMN1,T2.COLUMN2
ORDER BY T1.COLUMN1,T2.COLUMN2;
The %COLUMNS function returns all the column names specified in the SELECT clause.
Use:
RULE Get column names
ACTIVATE %COLUMNS
DATA Column name IS WHAT
THEN Column names retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Column name IS COLUMN1
Column name IS COLUMN2
The %DISTINCT function returns true if DISTINCT was specified in the SELECT clause; otherwise, it returns false.
Use:
RULE Get distinct
ACTIVATE %DISTINCT
DATA Distinct columns
THEN Distinct determined
Based on the sample SQL, the SIMPLEFACT Distinct columns will be false. Distinct columns would be true if the sample had been:
SELECT DISTINCT T1.COLUMN1,...
The %FUNCOLS function returns all the functions with column names specified in the SELECT clause.
Use:
RULE Get functions with columns
ACTIVATE %FUNCOLS
DATA Function column IS WHAT
THEN Functions with columns retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Function column IS AVG.COLUMN4
Function column IS MAX.COLUMN5
The %FUNCTION function returns all the function names specified in the SELECT clause.
Use:
RULE Get functions
ACTIVATE %FUNCTION
DATA Function name IS WHAT
THEN Functions retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Function name IS AVG
Function name IS MAX
The %GROUPBY function returns all the column names specified in the GROUP BY clause.
Use:
RULE Get group bys
ACTIVATE %GROUPBY
DATA Group by column IS WHAT
THEN Group bys retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Group by column IS COLUMN1
Group by column IS COLUMN2
The %LEFTREL function returns the left-hand side of the relational condition as specified in the WHERE clause.
Use:
RULE Get left relational column
ACTIVATE %LEFTREL
DATA Left relational column IS WHAT
THEN Left relational column retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Left relational column IS INDEX1
Left relational column IS COLUMN3
The %ORDERBY function returns all the column names specified in the ORDER BY clause.
Use:
RULE Get group bys
ACTIVATE %ORDERBY
DATA Order by column IS WHAT
THEN Order bys retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Order by column IS COLUMN1
Order by column IS COLUMN2
The %ORNOTERR function returns true if a faulty OR/NOT condition is specified in a WHERE clause.
Use:
RULE Determine faulty relational condition
ACTIVATE %ORNOTERR
DATA Bad or not condition
THEN Faulty relational determined
Based on the sample SQL, the SIMPLEFACT Bad or not condition will be false. If the sample had been
SELECT T1.COLUMN1
FROM CREATOR.TABLE1 T1
WHERE (NOT T1.COLUMN9 = 'RED' OR NOT T1.COLUMN9 = 'BLUE');
Then, when COLUMN9 is RED, it is not BLUE, so the OR makes the condition true; and when COLUMN9 is BLUE, it is not RED, so the OR still makes the condition true; and when COLUMN9 is neither RED nor BLUE, the condition is still true. The effect is the same as coding SELECT T1.COLUMN1 without a WHERE clause.
In this case, the Bad or not condition will be true.
The %RELATION function returns all the relational statements specified in the WHERE clause.
Use:
RULE Get relational statements
ACTIVATE %RELATION
DATA Relational statement IS WHAT
THEN Relational statements retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Relational statement IS INDEX1.EQ.INDEX2
Relational statement IS COLUMN3.GT.19951231
The %RELOPCOL function returns all the relational column/operator combinations and determines whether they compare against a literal or a field as specified in the WHERE clause.
Use:
RULE Get relational column and operator
ACTIVATE %RELOPCOL
DATA Relational IS WHAT
THEN Relational column and operator retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Relational IS CREATOR.TABLE1.INDEX1.EQ.FIELD
Relational IS CREATOR.TABLE1.COLUMN3.GT.LITERAL
The %RELOPS function returns all the relational operators and determines whether they compare against a literal or a field as specified in the WHERE clause.
Use:
RULE Get relational operator
ACTIVATE %RELOPS
DATA Relational operator IS WHAT
THEN Relational operator retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Relational operator IS EQ
Relational operator IS NOT.GT
The %RELTABS function returns all the relational conditions between tables as specified in the WHERE clause.
Use:
RULE Get relational table conditions
ACTIVATE %RELTABS
DATA Table relations ARE WHAT
THEN Relational table conditions retrieved
Based on the sample SQL, the following attribute-value pair will be true:
Relational statement IS TABLE1.EQ.TABLE2
The %RIGHTREL function returns the right-hand side of the relational condition when it references a field as specified in the WHERE clause.
Use:
RULE Get right relational column
ACTIVATE %RIGHTREL
DATA Right relational column IS WHAT
THEN Right relational column retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Right relational column IS INDEX2
The %TABLES function returns all the table names specified in the FROM clause.
Use:
RULE Get table names
ACTIVATE %TABLES
DATA Table IS WHAT
THEN table names retrieved
Based on the sample SQL, the following attribute-value pairs will be true:
Table IS TABLE1
Table IS TABLE2
The following examples suggest more uses of custom rules. The goal of every rule is DBA Rules Concluded. The THEN statement should not change to ensure that the Governor finishes its conclusion properly.
Example 1:
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 functions
RULE Get information about the query
ACTIVATE %TABLES
DATA Table 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 2:
This example illustrates how to check for one or more table names used in the original SQL request. For example, this rule file might have been built for TABLE2, and you know you never want TABLE1 used with TABLE2.
! Assuming this rule file is for TABLE2, do not allow joins to TABLE1
RULE Check table name
IF All functions processed
AND Table IS TABLE1
THEN DBA Rules Concluded
AND Run := "F"
AND Reason := "TABLE"
AND Rule Number := 9999
Example 3:
This rule example assures that if an equality condition exists on an indexed column in a WHERE clause, the request will run.
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 4:
This rule example cancels all requests using a SELECT * and no WHERE clauses.
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 5:
The following rule example ensures no requests are canceled.
RULE Do not cancel any queries
THEN DBA Rules Concluded
AND Run := "T"
| Information Builders |