A procedure executes 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 along with the SQL statements within the procedure execute 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 executed 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;
Scenarios
The following three scenarios explain the priority scheduling for the procedure execution:
Scenario 1
LOGON user1/user1, acct1; CALL prio2() /* this, along with the SQL statements inside */; /* the procedure are executed at the */; /* priority associated with acct1 */;
Scenario 2
LOGON user1/user1, acct1;
CALL prio2() /* this, along with the SQL statements inside */;
/* the procedure are executed at the */;
/* priority associated with acct1 */;
SET SESSION ACCOUNT acct2 FOR REQUEST;
CALL prio2() /* this, along 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 executing the STMT 1. The STMT 2 and STMT 3 (along with the SQL requests inside prio1) execute 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 */;