Teradata Query Scheduler and SQL Procedures - Advanced SQL Engine - Teradata Database

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.