Stored Procedure to Create Workload Management Rules - Teradata Vantage

There is a stored procedure that can be run to create the workload management rules for OTF. The Stored Procedure is named as TD_OTFDB.TDWMCreateOTFRulesSP. Running this stored procedure will create the following two rules:

• POG_OTF_ALL: A query limit rule that limits the number of concurrent OTF queries to three.
• POG_OTF_ALL_MM: A query classification rule that classifies OTF queries to run on medium timeshare priority.

These TASM rules can be viewed through Viewpoint or the tdwmdump utility. They are created as external rules, allowing users to modify them if needed in the future.

The parameters for the Stored Procedure are as follows:

Input Parameter

iRulesetName - Specific ruleset name in which new OTF rules need to be created.

Output Parameter

• oRulesetID - returns rulesetID for the specific input ruleset.
• oQueryLimitRuleID - returns rule ID of the newly created QueryLimit rule.
• oTimeshareRuleID - returns rule ID of the newly created Timeshare rule.
• oQueryLimitRuleName - returns rule name of the newly created QueryLimit rule.
• oTimeshareRuleName - returns rule name of the newly created Timeshare rule.
• Status (Meaning):
  • 0 = Success
  • 1 = Invalid Usage
  • 2 = SQL Processing Error
  • 3 = Internal Error
• ErrInfo - Contains error info if Status <> 0.

The following example shows the process to run the stored procedure:

CALL  TD_OTFDB.TDWMCreateOTFRulesSP(
            'firstconfig',     
             oRulesetID,     
             oRulesetName,     
             oQueryLimitRuleName,     
             oTimeshareRuleName,     
             Status,     
             ErrInfo);