Examples: ClassificationForTargetV - Teradata VantageCloud Lake

Example 1: List classification sub-criteria of each rule in 'TestConfig' ruleset

Query

SELECT RuleName, RuleType, TargetType AS ObjType, TargetValue AS ObjValue,
       TargetClassificationType AS TarClassType, 
       TargetClassificationValue AS TarClassVal, 
       TargetClassificationOperator AS TarClassOp
FROM TDWM.ClassificationForTargetV
WHERE RulesetName = 'TestConfig' 
ORDER BY RuleName, TargetValue, TargetType;

Output

RuleName      RuleType          ObjType     ObjValue      TarClassType  TarClassVal   TarClassOp
------------  ----------------  ----------  ------------  ------------  ------------  ----------
HugeFTS       Filter            TABLE       HUGE_TABLE    FTSCAN        Y             I
VLTable_Join  System Throttle   TABLE       VLDB.VLTABLE  MINSTEPROWS   10000000      I
VLTable_Join  System Throttle   TABLE       VLDB.VLTABLE  STMT          M             I
VLTable_Join  System Throttle   TABLE       VLDB.VLTABLE  MINACCPCT     40            I
VLTable_Join  System Throttle   TABLE       VLDB.VLTABLE  JOIN          A             I

...

Example 2: Recreate System Throttle

This example illustrates step #3 in Example setup: Recreate/Clone a System Throttle (Adding the same classification sub-criteria of 'TableA_FTS' in 'TestRuleset' to the new throttle).

BTEQ script

/*****************************************************************
* Step 3:  Add rule sub-classification criteria (if any)
*****************************************************************/

.SET TITLEDASHES OFF
.EXPORT DATA FILE=TDWM_RuleInfo_Temp_P3.exp
SELECT RuleName                     (TITLE ''),
          TargetType                   (TITLE ''),
          TargetValue                  (TITLE ''),
          TargetClassificationType     (TITLE ''),
          TargetClassificationValue    (TITLE ''),
          TargetClassificationOperator (TITLE ''),
          TargetClassificationDesc     (TITLE '')
   FROM TDWM.ClassificationForTargetV
   WHERE RulesetName = 'TestRuleset'
     AND RuleType = 'System Throttle'
     AND RuleName = 'TableA_FTS';
.EXPORT RESET

.SET TITLEDASHES ON
.IMPORT DATA FILE=TDWM_RuleInfo_Temp_P3.exp;
.REPEAT *
using (RuleName                       VARCHAR(30), 
       TargetType                     VARCHAR(20),
       TargetValue                    VARCHAR(30),
       TargetClassificationType       VARCHAR(20),
       TargetClassificationValue      VARCHAR(30), 
       TargetClassificationOperator   VARCHAR(6),
       TargetClassificationDesc       VARCHAR(80))
CALL TDWM.TDWMAddClassificationForTarget(
     'ProdRuleset'                  /* RulesetName */,
     :RuleName                      /* RuleName */,
     :TargetType                    /* TargetType */,
     :TargetValue                   /* TargetValue */,
     :TargetClassificationDesc      /* Description */,
     :TargetClassificationType      /* ClassificationType */,
     :TargetClassificationValue     /* ClassificationValue */,
     :TargetClassificationOperator  /* ClassificationOperator */,
     'N'                            /* ReplaceOption */);
.IMPORT RESET

Result

'TableA_FTS' in 'ProdRuleset' has the same classification criteria as 'TableA_FTS' in 'TestRuleset'.

Querying TDWM.ClassificationForTargetV (for sub-criteria) and TDWM.ClassificationForRuleV (for criteria) returns the following results for the source and new throttle

                 RulesetName TestRuleset
                    RuleName TableA_FTS
                  TargetType TABLE
                 TargetValue myDB.TableA
    TargetClassificationType FTSCAN
   TargetClassificationValue Y
TargetClassificationOperator I
                 RulesetName TestRuleset
                    RuleName TableA_FTS
                  TargetType TABLE
                 TargetValue myDB.TableA
    TargetClassificationType MINSTEPTIME
   TargetClassificationValue 3600
TargetClassificationOperator I


                 RulesetName ProdRuleset
                    RuleName TableA_FTS
                  TargetType TABLE
                 TargetValue myDB.TableA
    TargetClassificationType FTSCAN
   TargetClassificationValue Y
TargetClassificationOperator I
                 RulesetName ProdRuleset
                    RuleName TableA_FTS
                  TargetType TABLE
                 TargetValue myDB.TableA
    TargetClassificationType MINSTEPTIME
   TargetClassificationValue 3600
TargetClassificationOperator I


/* Recheck ClassificationTarget value adding sub-criteria */

           RulesetName TestRuleset
              RuleName TableA_FTS
    ClassificationType APPL
   ClassificationValue WebApp
ClassificationOperator I
    ClassificationDesc
  ClassificationTarget N
           RulesetName TestRuleset
              RuleName TableA_FTS
    ClassificationType TABLE
   ClassificationValue myDB.TableA
ClassificationOperator I
    ClassificationDesc
  ClassificationTarget Y


           RulesetName ProdRuleset
              RuleName TableA_FTS
    ClassificationType APPL
   ClassificationValue WebApp
ClassificationOperator I
    ClassificationDesc
  ClassificationTarget N
           RulesetName ProdRuleset
              RuleName TableA_FTS
    ClassificationType TABLE
   ClassificationValue myDB.TableA
ClassificationOperator I
    ClassificationDesc
  ClassificationTarget Y                 (sub-criteria added)

Example 3: Diagnose an Unexpected Query Filter

This example illustrates using TDWM.ClassificationForRuleV to diagnose an unexpected query failure due to a filter rule.

Suppose the current ruleset is 'TestConfig' and a SELECT statement fails with error 3149 as shown.

SELECT * FROM db1.xltable;

 *** Failure 3149 TDWM Filter violation for Query Request: For Rule Name 'Filter_XLtable'

The error message indicates the failure is due to the filter named 'Filter_XLTable'.

1. Use the view TDWM.ClassificationForRuleV to retrieve classification criteria of 'Filter_XLTable' in the ruleset 'TestConfig'.

   Query

   SELECT * FROM TDWM.ClassificationForRuleV
   WHERE RulesetName = 'TestConfig'
     AND RuleName = 'Filter_XLTable';

   Result:

              RulesetName TestConfig
                   RuleId         917
                 RuleName Filter_XLtable
                 RuleType Filter
       ClassificationType TABLE
      ClassificationValue DB1.XLtable
   ClassificationOperator I
     ClassificationTarget Y
       ClassificationDesc

   • One row indicates only one classification criterion.
   • The ClassificationType and ClassificationValue columns indicate the classification criterion is on the table 'DB1.XLtable' which matches the table in the failed SELECT statement.
   • The value of ClassificationTarget is 'Y' which indicates the presence of sub-criteria. So, the next step is to retrieve sub-criteria.
2. Use TDWM.ClassificationForTargetV to retrieve classification sub-criteria of 'Filter_XLTable'.

   Query

   SELECT * FROM TDWM.ClassificationForTargetV
   WHERE RulesetName = 'TestConfig'
     AND RuleName = 'Filter_XLTable';

   Result

                    RulesetName TestConfig
                         RuleId         917
                       RuleName Filter_XLtable
                       RuleType Filter
                     TargetType TABLE
                    TargetValue DB1.XLtable
       TargetClassificationType FTSCAN
      TargetClassificationValue Y
   TargetClassificationOperator I
       TargetClassificationDesc

   • One row indicates only one classification sub-criterion.
   • The TargetClassificationType and TargetClassificationValue columns indicate the sub-criterion is FTSCAN (for full table scan).
3. Diagnosis and correction:
   • Diagnosis: Based on rule Filter_XLtable, the SELECT statement is failing because it is performing a full table scan on table 'DB1.XLtable' which is prohibited by the rule.
   • Correction: If possible, add a WHERE clause to the SELECT statement so that it doesn't perform a full table scan.

     SELECT * FROM db1.xltable where c1 = 1;

                 c1           c2
        -----------  -----------
                  1            2