How an Application Works With a Stored Procedure

A typical stored procedure contains two or more SQL statements, and some manipulative or logical processing. In this example, your application, CALLSTPR, runs on a workstation client and calls a stored procedure, GETEMPSVR. The following process occurs:

  1. The workstation application establishes a connection to the DB2 LUW Server.
  2. The SQL statement CALL tells the DB2 LUW Server that the application is going to run the stored procedure, GETEMPSVR. The calling application provides the necessary parameters.
  3. The DB2 LUW Server searches the system tables for rows associated with stored procedure GETEMPSVR.
  4. The DB2 LUW Server passes information about the request to the stored procedure.
  5. The stored procedure executes SQL statements.
  6. The stored procedure assigns values to the output parameters and then exits.
  7. Control returns to the calling application, which receives the output parameters.

The application can call more stored procedures or it can execute more SQL statements. The application designer determines whether to COMMIT work in the stored procedure that runs on the server, or in the client as a single transaction.