Example of a simple stored procedure

When an application that runs on a workstation calls a stored procedure on a Db2 server, the stored procedure updates a table based on the information that it receives from the application.

Suppose that an application runs on a workstation client and calls a stored procedure A on the Db2 server at location LOCA. Stored procedure A performs the following operations:
  1. Receives a set of parameters containing the data for one row of the employee to project activity table (DSN8C10.EMPPROJACT). These parameters are input parameters in the SQL statement CALL:
    • EMP: employee number
    • PRJ: project number
    • ACT: activity ID
    • EMT: percent of employee's time required
    • EMS: date the activity starts
    • EME: date the activity is due to end
  2. Declares a cursor, C1, with the option WITH RETURN, that is used to return a result set containing all rows in EMPPROJACT to the workstation application that called the stored procedure.
  3. Queries table EMPPROJACT to determine whether a row exists where columns PROJNO, ACTNO, EMSTDATE, and EMPNO match the values of parameters PRJ, ACT, EMS, and EMP. (The table has a unique index on those columns. There is at most one row with those values.)
  4. If the row exists, executes an SQL statement UPDATE to assign the values of parameters EMT and EME to columns EMPTIME and EMENDATE.1
  5. If the row does not exist (SQLCODE +100), executes an SQL statement INSERT to insert a new row with all the values in the parameter list.1
  6. Opens cursor C1. This causes the result set to be returned to the caller when the stored procedure ends.
  7. Returns two parameters, containing these values:
    • A code to identify the type of SQL statement last executed: UPDATE or INSERT.
    • The SQLCODE from that statement.
Note:
  1. Alternatively, steps 4 and 5 can be accomplished with a single MERGE statement.
The following figure illustrates the steps that are involved in executing this stored procedure.
Figure 1. Stored procedure overview
Begin figure summary.This figure shows communication between a workstation and the stored procedures address space.Detailed description available.
Notes:
  1. The workstation application uses the SQL CONNECT statement to create a conversation with Db2.
  2. Db2 creates a Db2 thread to process SQL requests.
  3. The SQL statement CALL tells the Db2 server that the application is going to run a stored procedure. The calling application provides the necessary parameters.
  4. The plan for the client application contains information from catalog table SYSIBM.SYSROUTINES about stored procedure A.
  5. Db2 passes information about the request to the stored procedures address space, and the stored procedure begins execution.
  6. The stored procedure executes SQL statements.

    Db2 verifies that the owner of the package or plan containing the SQL statement CALL has EXECUTE authority for the package associated with the Db2 stored procedure.

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

    Any SQLCODE that is issued within an external stored procedure is not returned to the workstation application in the SQLCA (as the result of the CALL statement).

  7. If an error is not encountered, the stored procedure assigns values to the output parameters and exits.

    Control returns to the Db2 stored procedures address space, and from there to the Db2 system. If the stored procedure definition contains COMMIT ON RETURN NO, Db2 does not commit or roll back any changes from the SQL in the stored procedure until the calling program executes an explicit COMMIT or ROLLBACK statement. If the stored procedure definition contains COMMIT ON RETURN YES, and the stored procedure executed successfully, Db2 commits all changes. The COMMIT statement closes the cursor unless it is declared with the WITH HOLD option.

  8. Control returns to the calling application, which receives the output parameters and the result set. Db2 then:
    • Closes all cursors that the stored procedure opened, except those that the stored procedure opened to return result sets.
    • 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. Db2 receives and processes the COMMIT or ROLLBACK request. The COMMIT or ROLLBACK operation covers all SQL operations, whether executed by the application or by stored procedures, for that unit of work.

    If the application involves IMS or CICS®, similar processing occurs based on the IMS or CICS sync point rather than on an SQL COMMIT or ROLLBACK statement.

  9. Db2 returns a reply message to the application describing the outcome of the COMMIT or ROLLBACK operation.
  10. The workstation application executes the following steps to retrieve the contents of table EMPPROJACT, which the stored procedure has returned in a result set:
    1. Declares a result set locator for the result set being returned.
    2. Executes the ASSOCIATE LOCATORS statement to associate the result set locator with the result set.
    3. Executes the ALLOCATE CURSOR statement to associate a cursor with the result set.
    4. Executes the FETCH statement with the allocated cursor multiple times to retrieve the rows in the result set.
    5. Executes the CLOSE statement to close the cursor.