Called Procedure Priority - Teradata Vantage

A procedure runs at the priority the user session is running at the time the CALL statement is submitted. If you specify a SET SESSION ACCOUNT at a REQUEST level and the CALL statement is submitted, the procedure with the SQL statements within the procedure run at the specified priority.

The account is set to the previous account after the CALL statement is completed.

If the user priority is changed asynchronously during procedure execution, the new priority takes effect for all the subsequent requests that are submitted as part of procedure execution.

If the asynchronous change is at the request level, the priority change applies to all the statements run as part of the CALL statement.

Consider the following SQL procedure:

     CREATE PROCEDURE prio2()
     BEGIN
       INSERT INTO temp(1, 'stored procedure before prio1') /* STMT1 */;
       CALL prio1()                                         /* STMT2 */;
       INSERT INTO temp(2, 'stored procedure after prio1')  /* STMT3 */;
     END;

The following three scenarios explain the priority scheduling for the procedure execution:

Scenario 1

     LOGON user1/user1, acct1;
     CALL prio2() /* this, with the SQL statements inside       */;
                  /* the procedure are executed at the          */;
                  /* priority associated with acct1             */;

Scenario 2

      LOGON user1/user1, acct1;
     CALL prio2() /* this, with the SQL statements inside */;
                  /* the procedure are executed at the    */;
                  /* priority associated with acct1       */;
     SET SESSION ACCOUNT acct2 FOR REQUEST;
     CALL prio2() /* this, with the SQL statements inside */;
                  /* the procedure are executed at the   */;
                  /* priority associated with acct2             */;
     SELECT * FROM temp /* this is executed at the priority     */;
                        /* associated with acct1                */;

Scenario 3

Assume that the priority is changed for user1 to acct2, after running the STMT 1. The STMT 2 and STMT 3 (with the SQL requests inside prio1) run in the changed priority. If the priority is changed at request level, the session priority is restored to that corresponding to acct1 at the end of the execution of prio2.

     LOGON user1/user1, acct1;
     CALL prio2() /* this is executed at the priority associated */;
                  /* with acct1                                  */;