17.10 - Examples - Advanced SQL Engine - Teradata Database

Teradata Vantageâ„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Create System Throttle or ARM

In the MyFirstConfig ruleset, create a member system throttle called TableA_FTS to set a concurrency limit of 1 with delay action for each user for requests that perform full table scans on the myDB.TableA table with estimated minimum total processing time of 3600 seconds from the WebApp application. This throttle is cannot be overridden.

The TASM rule attributes are:

  • Ruleset name: MyFirstConfig
  • System throttle name: TableA_FTS
  • Description: Member throttle FTS on myDB.TableA from WebApp
  • Throttle type: Member
  • Qualifications:
    • Application: WebApp
    • Table: myDB.TableA
    • Subcriteria on myDB.TableA: Full Table Scan and minimum total process time of 3600 seconds
  • Limit: 1
The API call sequence:
  1. The desired ruleset name, MyFirstConfig, is known so it is not necessary to query the TDWM.Configurations table.
  2. Create a system throttle called TableA_FTS without any classification criteria.
    CALL TDWM.TDWMCreateSystemThrottle(
      'MyFirstConfig',    /* ruleset name */
      'TableA_FTS',       /* throttle name */
      'Member throttle FTS on myDB.TableA from WebApp' /* description */,
      'DM',               /* 'D': disable override, 'M': member type */
      'N'                 /* not replace */);
  3. Add classification criteria.
    • Add application criterion:
      CALL TDWM.TDWMAddClassificationForRule(
        'MyFirstConfig',               /* ruleset name */
        'TableA_FTS',                  /* rule name */
        'Application classification',  /* description */
        'APPL',                        /* application criterion type */
        'WebApp',                      /* APPL = WebApp */
        'I',                           /* Inclusion criterion APPL = WebApp */
        'N'                            /* Not a replace */);
    • Add table type criterion:
      CALL TDWM.TDWMAddClassificationForRule(
        'MyFirstConfig',              /* ruleset name */
        'TableA_FTS',                 /* rule name */
        'Table classification',       /* description */
        'TABLE',                      /* table criterion type */
        'myDB.TableA',                /* TABLE = myDB.TableA */
        'I',                          /* Inclusion criterion */
        'N'                           /* Not a replace */);
  4. Add sub-criteria for table criterion.
    • Add full table scan sub-criterion for target myDB.TableA:
      CALL TDWM.TDWMAddClassificationForTarget( 
        'MyFirstConfig',              /* ruleset name */
        'TableA_FTS',                 /* rule name */
        'TABLE',                      /* Target: TABLE criterion type */
        'myDB.TableA',                /* Target: TABLE = myDB.TableA */
        'FTSCAN sub-criterion',       /* description */
        'FTSCAN',                     /* full table scan type */
        NULL,                         /* TargetValue not needed */ 
        'I',                          /* Inclusion criterion FTSCAN */
        'N'                           /* Not a replace */);
    • Add minimum step time sub-criterion for target myDB.TableA:
      CALL TDWM.TDWMAddClassificationForTarget( 
        'MyFirstConfig',               /* ruleset name */
        'TableA_FTS',                  /* rule name */
        'TABLE',                       /* Target: TABLE criterion type */
        'myDB.TableA',                 /* Target: TABLE = myDB.TableA */
        'Min step time sub-criterion', /* description */
        'MINSTEPTIME',                 /* minimum step time */
        '3600',                        /* min >= 3600 seconds */
        'I',                           /* Inclusion criterion */
        'N'                            /* Not a replace */);
  5. Set the default limit for the system throttle:
    CALL TDWM.TDWMAddLimitForRuleState(
      'MyFirstConfig',               /* ruleset name */
      'TableA_FTS',                  /* rule name */
      'Default’,                     /* state name */
      'Default limit',               /* description */
      '1',                           /* limit */
      'D'                            /* delay */
      'N'                            /* Not a replace */);
  6. Enable the system throttle:
    CALL TDWM.TDWMManageRule(
      'MyFirstConfig',               /* ruleset name */
      'TableA_FTS',                  /* rule name */
      'E'                            /* enable throttle */);
  7. Call TDWM.TDWMActivateRuleset to activate the MyFirstConfig ruleset with the new throttle.
    CALL TDWM.TDWMActivateRuleset(
      'MyFirstConfig'      /* ruleset name */);
    

Delete System Throttle or ARM

  1. If you want to delete a system throttle (or an ARM) in a specific ruleset, retrieve the desired ruleset name from the TDWM.Configurations table.
  2. Call the XSP TDWM.TDWMDeleteRule to delete a system throttle (or an ARM) by specifying its name.
    CALL TDWM.TDWMDeleteRule(
      'MyFirstConfig',         /* ruleset name */
      'Throttle_AcctDDL'       /* throttle name */);
  3. Call the XSP TDWM.TDWMActivateRuleset to activate the updated MyFirstConfig ruleset, which no longer has the system throttle (or ARM).
    CALL TDWM.TDWMActivateRuleset(
      'MyFirstConfig'          /* ruleset name */);