Examples: TDWM.RuleInfoV - Teradata VantageCloud Lake

Example 1: List rules in the ruleset 'Comp_ruleset1' and their attributes

Query

SELECT RuleId, RuleName, RuleType, RuleStatus, RuleAttributes AS RuleAttr, 
       RuleDescription AS RuleDesc
FROM TDWM.RuleInfoV 
WHERE RulesetName = 'Comp_ruleset1'
ORDER BY RuleType, RuleName;

Output

RuleId  RuleName                     RuleType             RuleStatus  RuleAttr  RuleDesc
------  ---------------------------  -------------------  ----------  --------  --------------
   721  FILTER_DSSACCT5              Filter               E           L         Filter All-AMP …
   720  FILTER_DSSUSER9              Filter               E
   723  ARM_DSSACT123                Meter                E           C
   ...
   736  AWT_UTIL                     Res Limit - AWT      E           C         Utility AWT …
   729  SESTHROTTLE_DSS              Session Throttle     E           C
   ...
   727  THROTTLE_DSSACCTSTR123       System Throttle      E           I
   726  THROTTLE_DSSPROF123          System Throttle      E           M         Query throttle …
   728  THROTTLE_TACTUSER            System Throttle      E           C
   730  UTIL_LOAD                    Utility Limit        E           C
   ...
   715  FastLoad+MultiLoad-Default   Utility Session      E           C
   717  FastLoad+MultiLoad-Large     Utility Session      E           C
   716  FastLoad+MultiLoad-Small     Utility Session      E           C
   737  VP1_THROTTLE                 VPartition Throttle  E           C
   ...
   732  WD_DSSLG1                    Workload             E           C
   734  WD_DSSTS1                    Workload             E           C
   731  WD_TACT1                     Workload             E           C
   733  WD_UTILLOAD                  Workload             E           C
   735  WD_UTILOTHER                 Workload             E           C
   738  WDGRP_DSS                    Workload Group       E           C

Example 2: Create a new throttle

This example illustrates step #1 in Example setup: Recreate/Clone a System Throttle (Create a new throttle 'TableA_FTS' in 'ProdRuleset' with the same attributes of 'TableA_FTS' in 'TestRuleset' (but without classification criteria, classification sub-criteria, and state limit)).

BTEQ script

/*****************************************************************
* Step 1:  Create new rule with same attributes.
*****************************************************************/

.SET TITLEDASHES OFF
.EXPORT DATA FILE=TDWM_RuleInfo_Temp_P1.exp
SELECT RuleName        (TITLE ''),
       RuleAttributes  (TITLE ''),
       RuleDescription (TITLE '')
FROM TDWM.RuleInfoV
WHERE RulesetName = 'TestRuleset'
  AND RuleType = 'System Throttle'
  AND RuleName = 'TableA_FTS';
.EXPORT RESET

.SET TITLEDASHES ON
.IMPORT DATA FILE=TDWM_RuleInfo_Temp_P1.exp;
.REPEAT *
using (RuleName         VARCHAR(30), 
       RuleAttributes   VARCHAR(20), 
       RuleDescription  VARCHAR(80))
call TDWM.TDWMCreateSystemThrottle (
     'ProdRuleset'          /* RulesetName */,
     :RuleName              /* ThrottleName */, 
     :RuleDescription       /* Description */,
     :RuleAttributes        /* Attributes */,
     'N'                    /* ReplaceOption */);
.IMPORT RESET

Result

'TableA_FTS' is created in 'ProdRuleset' with the same rule attributes as 'TableA_FTS' in 'TestRuleset'.

Querying the view TDWM.RuleInfoV returns the same results for the source and new throttle except that RuleStatus of 'TableA_FTS' in 'ProdRuleset' is 'D' (disabled).

    RulesetName TestRuleset
       RuleName TableA_FTS
       RuleType System Throttle
     RuleStatus E
 RuleAttributes DM
RuleDescription Member throttle FTS on myDB.TableA from WebApp

    RulesetName ProdRuleset
       RuleName TableA_FTS
       RuleType System Throttle
     RuleStatus D
 RuleAttributes DM
RuleDescription Member throttle FTS on myDB.TableA from WebApp

Example 3: Analyze the Effectiveness of a System Throttle

Suppose the throttle is 'Throttle_Profile1' in the ruleset 'TestConfig'. The TDWMRuleID column in the view DBC.QryLog indicates ID of the TASM rule which caused the delay.

The following SQL statement retrieves RuleId of 'Throttle_Profile1' from TDWM.RuleInfoV to join with DBC.QryLog to get number of delayed queries, minimum, maximum, and average delay time.

Query

SELECT COUNT(*) AS DelayCount, MIN(DelayTime) AS MIN_DelayTime, MAX(DelayTime) AS MAX_DelayTime, AVG(DelayTime) AS AVG_DelayTime
FROM TDWM.RuleInfoV R, DBC.QryLog Q
WHERE R.RulesetName = 'TestConfig'
  AND R.Rulename = 'Throttle_Profile1'
  AND R.RuleId = Q.TDWMRuleID
  AND Q.DelayTime > 0
  AND Q.StartTime >= TIMESTAMP '2025-04-14 00:00:00' and Q.StartTime < TIMESTAMP '2025-04-21 00:00:00';

Output

 DelayCount   MIN_DelayTime   MAX_DelayTime   AVG_DelayTime
-----------  --------------  --------------  --------------
     205883           0.010           4.180           0.377