Examples: TDWM.RuleStateLimitV - Teradata VantageCloud Lake

Example 1: List state limits of existing rules in ruleset 'Comp_ruleset1'

Query

SELECT RuleId, RuleName, RuleType, RuleStateName AS State, 
       RuleStateLimit AS StateLimit, RuleStateAction AS Action
FROM TDWM.RuleStateLimitV WHERE RulesetName = 'Comp_ruleset1'
ORDER BY RuleType, RuleName, RuleStateName;

Output

RuleId  RuleName               RuleType             State     StateLimit     Action
------  ---------------------  -------------------  --------  -------------  ------
   721  FILTER_DSSACCT5        Filter               Default   ?              N
   721  FILTER_DSSACCT5        Filter               State1    ?              E
   720  FILTER_DSSUSER9        Filter               Default   ?              E
   720  FILTER_DSSUSER9        Filter               State1    ?              N
   723  ARM_DSSACT123          Meter                Default   15/M QT=0      D
   723  ARM_DSSACT123          Meter                State1    5/M QT=0       D
   722  ARM_DSSUSER            Meter                Default   20/M QT=0      D
        ...
   736  AWT_UTIL               Res Limit - AWT      Default   50             D
   729  SESTHROTTLE_DSS        Session Throttle     Default   100            D
        ...
   726  THROTTLE_DSSPROF123    System Throttle      Default   1              D
   726  THROTTLE_DSSPROF123    System Throttle      State1    15             D
        ...
   730  UTIL_LOAD              Utility Limit        Default   15             D
   730  UTIL_LOAD              Utility Limit        State1    20             D
        ...
   712  FastExport-Default     Utility Session      Default   4              D
   714  FastExport-Large       Utility Session      Default   4              D
   713  FastExport-Small       Utility Session      Default   2              D
        ...
   737  VP1_THROTTLE           VPartition Throttle  Default   1200           D
   737  VP1_THROTTLE           VPartition Throttle  State1    550            D
        ...
   731  WD_TACT1               Workload             Default   NoLimit        D
   733  WD_UTILLOAD            Workload             Default   8              D
   733  WD_UTILLOAD            Workload             State1    15             D
   735  WD_UTILOTHER           Workload             Default   5              D
   738  WDGRP_DSS              Workload Group       Default   50             D
   738  WDGRP_DSS              Workload Group       State3    20             D

Example 2: Set the state limit for the new throttle

This example illustrates Step #4 (Set the same state limit of 'TableA_FTS' in 'TestRuleset' for the new throttle) and Step #5 (Enable rule and activate ruleset) in Example setup: Recreate/Clone a System Throttle.

BTEQ script

/*****************************************************************
* Step 4:  Add rule state limits and actions
*****************************************************************/

.SET TITLEDASHES OFF
.EXPORT DATA FILE=TDWM_RuleInfo_Temp_P4.exp
SELECT RuleName            (TITLE ''),
       RuleStateName       (TITLE ''),
       RuleStateLimit      (TITLE ''),
       RuleStateAction     (TITLE ''),
       RuleStateLimitDesc  (TITLE '')
FROM TDWM.RuleStateLimitV
WHERE RulesetName = 'TestRuleset'
  AND RuleType = 'System Throttle'
  AND RuleName = 'TableA_FTS';
.EXPORT RESET

.SET TITLEDASHES ON
.IMPORT DATA FILE=TDWM_RuleInfo_Temp_P4.exp;

.REPEAT *
using (RuleName                 VARCHAR(30),
       RuleStateName            VARCHAR(30),
       RuleStateLimit           VARCHAR(30),
       RuleStateAction          VARCHAR(6),
       RuleStateLimitDesc       VARCHAR(80))
CALL TDWM.TDWMAddLimitForRuleState(
     'ProdRuleset'              /* RulesetName */,
     :RuleName                  /* RuleName */,
     :RuleStateName             /* StateName */,
     :RuleStateLimitDesc        /* Description */,
     :RuleStateLimit            /* StateLimit */,
     :RuleStateAction           /* Action */,
     'N'                        /* ReplaceOption */);
.IMPORT RESET

/***************************************************************** 
* Step 5:  Enable the cloned rule.
*****************************************************************/

CALL TDWM.TDWMManageRule (
     'ProdRuleset'          /* RulesetName */,
     'TableA_FTS'           /* RuleName */,
     'E'                    /* Operation */);

Result

'TableA_FTS' in 'ProdRuleset' has the same state limit as 'TableA_FTS' in 'TestRuleset'.

Querying TDWM.RuleStateLimitV returns the following results:

       RulesetName TestRuleset
          RuleName TableA_FTS
     RuleStateName Default
    RuleStateLimit 1
   RuleStateAction D
RuleStateLimitDesc


       RulesetName ProdRuleset
          RuleName TableA_FTS
     RuleStateName Default
    RuleStateLimit 1
   RuleStateAction D
RuleStateLimitDesc

(Step #5) Activate ProdRuleset to apply the changes if ProdRuleset is the currently active ruleset.

See "Create System Throttle or ARM" in Ruleset API Examples