Example: Limiting the Number of Queries That a Specific User Can Run Concurrently - Teradata Viewpoint

Teradata Viewpoint User Guide
Product
Teradata Viewpoint
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
hkd1478709580913.ditamap
dita:ditavalPath
2206_User_vp_1610.ditaval.ditaval
dita:id
B035-2206
lifecycle
previous
Product Category
Analytical Ecosystem
Suppose you want to prevent overloading the system and improve the system throughput by limiting a single user's queries to process only one at a time. For example, John Smith has 25 queries that he submits before he leaves from work at night and he needs the results only when he returns. You can place any queries that exceed John's limit in a delay queue. When one query completes, the next query in the queue starts executing.
  1. Select a ruleset to edit.
    1. Open the Workload Designer portlet and the Workload Designer view.
    2. From the For System list, select PROD1.
    3. In the local Working section, click the ruleset name, FILTERS/THROTTLES.
  2. Create a new system throttle.
    1. From the ruleset toolbar, click Throttles.
    2. Next to System Throttles, click .
    3. Enter the name of the throttle, in this case John Smith Concurrent Queries.
    4. Select the Rule Type option Collective.
    5. Click Save.
  3. Add classification criteria to limit the rule to John Smith.
    1. Click the Classification tab.
    2. From the Add Classification Criteria list, select Request Source.
    3. Click Add. The Edit Request Source Criteria dialog box appears.
    4. From the Source Type list, select Username.
    5. Under Username, enter the user ID for the database session: johnsmith.
    6. Click Include.
    7. Click OK.
    8. Click Save.
  4. Add throttle limits to allow only one query to execute at a time.
    1. Click the State Specific Settings tab.
    2. Under Default Settings at the bottom of the Throttles view, type 1 in the box to set the concurrency limit.
    3. Select Delay to place queries exceeding the limit in a delay queue.
    4. Click Save.
  5. Activate the ruleset.
    1. Go back to the Workload Designer view.
    2. For the FILTERS/THROTTLES ruleset, click and select Make Active.
  6. [Optional] Review the queries in the delay queue.
    1. Open the Workload Monitor portlet.
    2. Click in the portlet frame and select Settings.
    3. Click the Systems tab.
    4. From the Systems list, select PROD1.
    5. Click OK.
    6. From the toolbar, click on Delayed Requests to view metrics for requests that were delayed by throttles.
    7. Click on the Session ID to view more details.