Advanced Database Feature Guide


Using Stored Procedures

An application can be designed to run in two parts, one on the client and the other on the server. The stored procedure is the part that runs at the database within the same transaction as the application. Stored procedures can be written in either embedded SQL or using the DB2 CLI functions (see "Writing a Stored Procedure in CLI"). In general, stored procedures have the following advantages:

In addition, stored procedures written in embedded static SQL have the following advantages:

Calling Stored Procedures

Stored procedures are invoked using a protocol on a connection protocol or via a procedure descriptor:

connection executeProcedure: <procedure name> 
- or - 
connection newProcedureDescriptor 
procedureName: <procedure name>; 
executeOn: connection. 
 

It is also possible to pass parameter values to the procedure using the following syntax:

connection 
executeProcedure: <procedure name> 
withParameterTypes: <collection: of parameter types> 
withParameterValues: <collection of parameter values> 
- or - 
connection newProcedureDescriptor 
procedureName: <procedure name>; 
parameterTypes: <collection of parameter types> 
withParameterValues: <collection of parameter types>. 
withParameterValues: <collection of <collection of parmateter values>

A stored procedure name can take one of the following forms:

procedure-name
The name (with no extension) of the procedure to execute. The procedure invoked is determined as follows.
  1. The procedure-name is used both as the name of the stored procedure library and the function name within that library. For example, if procedure-name is proclib, the DB2 server will load the stored procedure library named proclib and execute the function routine proclib() within that library.
  2. If the library or function could not be found, the procedure-name is used to search the defined procedures (in SYSCAT.PROCEDURES) for a matching procedure. A matching procedure is determined using the steps that follow.
    1. Find the procedures from the catalog (SYSCAT.PROCEDURES) where the PROCNAME matches the procedure-name specified and the PROCSCHEMA is a schema name in the function path.
    2. Next, eliminate any of these procedures that do not have the same number of parameters as the number of arguments specified in the CALL statement
    3. Chose the remaining procedure that is earliest in the function path.
    4. If there are no remaining procedures after step 2, an error is returned (SQLSTATE 42884).

Once the procedure is selected, DB2 will invoke the procedure defined by the external name.

procedure-name!func-name
The use of the exclamation sign allows the specification of a library name identified by procedure-name and the function to be executed is given by func-name. This allows similar function routines to be placed in the same stored procedure library.

/u/db2user/procedure-name!func-name
The name of the stored procedure library is specified as a full path name. The function to be executed is given by func-name.

For more information regarding the calling of stored procedures, refer to DB2’s SQL Reference and the Embedded SQL Programming Guide.

Registering Stored Procedures

For information on registering stored procedures on the server, see Appendix H. "Pseudo Catalog Table for Stored Procedure Registration" in the DB2’s Call Level Interface Guide and Reference.

If the stored procedure resides on a DB2 for MVS/ESA V4.1 or later server, the name of the stored procedure must be defined in the SYSIBM.SYSPROCEDURES catalog table. The pseudo catalog table used by DB2 Universal Database is a derivation and extension of the DB2 for MVS/ESA SYSIBM.SYSPROCEDURES catalog table).

If the stored procedure resides on a DB2 for AS/400 V3.1 server, the application must know the actual path and name of the stored procedure ahead of time. There is no real or pseudo catalog table to retrieve information on stored procedures or their argument list.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]