Invoking multiple instances of a stored procedure

Your application program can issue multiple CALL statements to the same local or remote stored procedure. Assume that your stored procedure returns result sets and the calling application leaves those result sets open before the next call to that same stored procedure. In that case, each CALL statement invokes a unique instance of the stored procedure.

About this task

When you invoke multiple instances of a stored procedure, each instance runs serially within the same Db2 thread and opens its own result sets. These multiple calls invoke multiple instances of any packages that are invoked while running the stored procedure. These instances are invoked at either the same or different level of nesting under one Db2 connection or thread.

For local stored procedures that issue remote SQL, instances of the applications are created at the remote server site. These instances are created regardless of whether result sets exist or are left open between calls.

If you call too many instances of a stored procedure or if you open too many cursors, Db2 storage shortages and EDM POOL FULL conditions might occur. If the stored procedure issues remote SQL statements to another Db2 server, these conditions can occur at both the Db2 client and at the Db2 server.

Procedure

To invoke multiple instances of a stored procedure:

  1. To optimize storage usage and prevent storage shortages, ensure that you specify appropriate values for the following two subsystem parameters:
    MAX_ST_PROC
    Controls the maximum number of stored procedure instances that you can call within the same thread.
    MAX_NUM_CUR
    Controls the maximum number of cursors that can be opened by the same thread.

    When either of the values from these subsystem parameters is exceeded while an application is running, the CALL statement or the OPEN statement receives SQLCODE -904.

  2. In your application, issue CALL statements to the stored procedure.
  3. In the calling application for the stored procedure, close the result sets and issue frequent commits. Even read-only applications should perform these actions.

    Applications that fail to close result sets or issue an adequate number of commits might terminate abnormally with Db2 storage shortage and EDM POOL FULL conditions.