16.20 - Teradata Query Scheduler and SQL Procedures - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
Teradata Query Scheduler is a workload management tool that empowers DBAs to perform any of the following tasks.
  • Set limits on concurrency within defined groups of users
  • Control which requests can run and which cannot based on dynamic criteria
  • Schedule requests to run at times when they do not overload the system

See Teradata® Query Scheduler Administrator Guide, B035-2511 and Teradata® Query Scheduler User Guide, B035-2512 for further information about SQL query workload management.

The SQL requests contained within procedures are subject to the same rule checking as any other SQL requests running on the system. However, the procedure itself is already launched and executing at the time the rules are applied. The SQL requests within the procedure must have already been submitted to the database before the query scheduler is able to check for their compliance with its rule set.

If there are multiple SQL requests in the procedure, it is possible that some of them pass the rules checking done by the query scheduler, while others do not. If any of the statements violates a rule defined by the query scheduler, the procedure aborts, as illustrated below, when notification of a rules violation by 1 of its SQL statements is made.

This error does not mean that previous SQL requests in the procedure did not complete successfully.

The following is a sample run of a procedure when 1 of the SQL requests within it is rejected by a query scheduler workload limit rule. A concurrency limit of 1 was set at the Performance Group level, and 1 of the SQL requests in the procedure would have violated this rule.

    CALL pksproc1(:k,balance1);
    
    *** Failure 3151 pksproc1:DQM Workload violation:
    User within Performance Group Limit: 2,
    For object R1, Regulation applies from 00:00 to 24:00

You can build a condition handler into the procedure body to check for error 3151 and then to branch to another point in the procedure should that error be received (see Teradata Vantage™ SQL Stored Procedures and Embedded SQL , B035-1148 for information about procedure condition handling). This technique keeps the procedure from being terminated before its normal completion.

The system does not notify the procedure if any of its SQL requests is delayed. In that case, the procedure just sleeps until it is allowed to run.