How an Application Works With a Stored Procedure

Restriction: This topic applies to Windows environments only.

A typical stored procedure contains two or more SQL statements, and some manipulative or logical processing that you cannot include in an SQL statement. In this example, your application, CALLSTPR, runs on a workstation client and calls a stored procedure, STPROC1. The following process occurs:

  1. The workstation application establishes a connection to the XDB Server.
  2. The SQL statement CALL tells the XDB Server that the application is going to run the stored procedure, STPROC1. The calling application provides the necessary parameters.
  3. The XDB Server searches the catalog table SYSIBM. SYSROUTINES for rows associated with stored procedure STPROC1.
  4. The XDB 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 XDB Server:
    • Closes all cursors that the stored procedure opened that weren't defined as WITH RETURN. Cursors that were defined as WITH RETURN are closed by the XDB Server when a COMMIT WORK command is issued if the WITH HOLD attribute was not declared on the stored procedure generated cursor.
    • Discards all SQL statements that the stored procedure prepared.
    • Reclaims the working storage that the stored procedure used.

    The application can call more stored procedures or it can execute more SQL statements.

  8. Once control is returned to the program that invoked the stored procedure, the XDB Server will treat all the work generated by the stored procedure (and subsequently by the calling program) as a single transaction.
Parent topic: Stored Procedures