Stored procedure processing

There are several steps to stored procedure processing.

The following figure illustrates processing without stored procedures.

Figure 1. Processing without stored procedures. An application embeds SQL statements and communicates with the server separately for each statement.
Begin figure description. A client and a DB2 system are shown. Three pairs of arrows from the client to the DB2 system and back represent processing for 3 embedded SQL statements. End figure description.

The following figure illustrates processing with stored procedures.

Figure 2. Processing with stored procedures. The same series of SQL statements uses a single send or receive operation.
Begin figure description. This figure shows processing with stored procedures, where the same series of SQL statements uses a single send or receive operation. End figure description.
Figure notes:
  • The workstation application uses the SQL CONNECT statement to create a conversation with Db2.
  • Db2 creates a Db2 thread to process SQL requests. A thread is the Db2 structure that describes the connection of an application and traces application progress.
  • The SQL statement CALL tells the Db2 server that the application is going to run a stored procedure. The calling application provides the necessary arguments.
  • Db2 processes information about the request and loads the stored procedure program.
  • The stored procedure executes SQL statements.

    One of the SQL statements opens a cursor that has been declared WITH RETURN. This action causes a result set to be returned to the workstation application.

  • The stored procedure assigns values to the output parameters and exits. Control returns to the Db2 stored procedures region and goes from there to the Db2 subsystem.
  • Control returns to the calling application, which receives the output parameters and the result set.

    The application can call other stored procedures, or it can execute additional SQL statements. Db2 receives and processes the COMMIT or ROLLBACK request. The commit or rollback operation covers all SQL operations that the application or the stored procedure executes during the unit of work, unless the procedure is defined with the AUTONOMOUS option. Autonomous procedures execute in a separate unit of work from the calling application.

    If the application involves IMS or CICS®, similar processing occurs. This processing is based on the IMS or CICS synchronization model, rather than on an SQL COMMIT or ROLLBACK statement.