Limited supportBMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Apptune for Db2 13.1.

Structure of rules

You store rules in a data set member.

The rules are stored in the BMCSAMP data set, in the following members:

  • PSSDFLT for default rules
  • PSSAPPL for rules for application developers
  • PSSJAPAN for rules in Japanese

Standard format for a rule set

The first line of the data set member contains the rule set declaration:

RULESET <ruleSetName>

Following the RULESET declaration, you can have one or more rules. Rules are processed in the order in which they are defined in the rule set. Each rule adheres to the following standard format, though the ELSE clause is optional.

<name>: IF <predicate>
      THEN OUT=<action1>
      ELSE OUT=<action2>

In this format, the variables are as follows:

  • name indicates the name of the rule and must be unique within the rule set. Do not include a space between the name of the rule and the colon.

  • predicate is an IF statement that specifies the value that is being evaluated and the value to which it is being compared. The predicate can include multiple conditions separated by AND or OR. 

     IF UPDATE_NO_WHERE = "Y"

    • IF MIN = "Y" OR MAX = "Y"
    • IF OPTIMIZE = "Y" AND (MIN = "Y" OR MAX = "Y" OR AVG = "Y" OR SUM = "Y")
    • IF PARTITIONS > 0

  • action1 value indicates the action that is performed if the condition within the predicate is true. Usually, the action specifies issuing a message. The message must end with /n and be enclosed in quotes. Vertical bars (||) indicate that the message continues across lines. 

    THEN OUT=OUT||"BMC184024I-Consider the use of BETWEEN in "||"
    place of >= and <=. DB2 may consider using an index to provide the"||"
    answer./n"

  • action2 indicates the action that is performed if the condition within the predicate is false. This value follows the same rules as action1.

Samples from the default rule set

Following are descriptions of sample rules (RULE1032, RULE2002, and RULE1026) from the default rule set.

  • RULE1032 triggers if OPTIMIZE and UNION are both used. The rule then produces a message that indicates that the rows will not be optimized due to this condition:
RULE1032: IF OPTIMIZE = "Y" AND UNION = "Y"                           
          THEN OUT=OUT||"BMC184032W-The OPTIMIZE FOR N ROWS will be"||
" ignored due to the use of a UNION, UNION ALL, or UNION DISTINCT./n"
  • RULE2002 triggers if VALIDATE is set to R. The rule then produces a message to indicate that this setting can impact performance:
 RULE2002: IF VALIDATE = "R"
          THEN OUT=OUT||"BMC184172I-The SQL and DB2 authorizations "||
"will be checked at execution time. This can adversely impact perfo"||
"rmance. Review with DBA on this usage./n"
  • RULE1026 provides two different message outputs by providing an ELSE statement. If QUALIFIED is set to N, one message is generated; otherwise, a different message is generated.
RULE1026: IF QUALIFIED = "N"                                          
          THEN OUT=OUT||"BMC184026I-The tables referenced in the FR"||
"OM clause are not qualified. These will be resolved at bind time b"||
"y the QUALIFIER name, by a SET CURRENT SQLID/SCHEMA if the query i"||
"s dynamic, or it will default to the executing AUTH ID./n"           
          ELSE OUT=OUT||"BMC184028I-The tables referenced in the FR"||
"OM clause are fully qualified. This will limit the flexibility for"||
" the qualifier to be resolved at bind time./n"

Related topic

Defining new expert rules

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*