Call Level Interface Guide and Reference

Calling Stored Procedures

Stored procedures are invoked from a DB2 CLI application by passing the following CALL statement syntax to SQLExecDirect() or to SQLPrepare() followed by SQLExecute().

                            +-,------+
                            V        |
>>-CALL--procedure-name--(----+----+-+--)----------------------><
                              +-?--+

Note:

Although the CALL statement cannot be prepared dynamically, DB2 CLI accepts the CALL statement as if it could be dynamically prepared.

Stored procedures can also be called using the ODBC vendor escape sequence shown in "Stored Procedure Call Syntax".

procedure-name

Specifies a stored procedure name, and it 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 use of the CALL statement and stored procedures, refer to the SQL Reference and the Embedded SQL Programming Guide.

If the server is DB2 Universal Database Version 2.1 or later, or DB2 for MVS/ESA V4.1 or later, SQLProcedures() can be called to obtain a list of stored procedures available at the database.
Note: For DB2 Universal Database, SQLProcedures() may not return all procedures, and applications can use any valid procedure, regardless of whether it is returned by SQLProcedures(). For more information, refer to "Registering Stored Procedures" and SQLProcedures - Get List of Procedure Names.

The ? in the CALL statement syntax diagram denote parameter markers corresponding to the arguments for a stored procedure. All arguments must be passed using parameter markers; literals, the NULL keyword, and special registers are not allowed. However, literals can be used if the vendor escape call statement is used, ie. the call statement is surrounded by curly braces '{...}'.

The parameter markers in the CALL statement are bound to application variables using SQLBindParameter(). Although stored procedure arguments can be used both for input and output, in order to avoid sending unnecessary data between the client and the server, the application should specify on SQLBindParameter() the parameter type of an input argument to be SQL_PARAM_INPUT and the parameter type of an output argument to be SQL_PARAM_OUTPUT. Those arguments that are both input and output have a parameter type of SQL_PARAM_INPUT_OUTPUT.

If the server is DB2 Universal Database Version 2.1 or later, or DB2 for MVS/ESA V4.1 or later, an application can call SQLProcedureColumns() to determine the type of a parameter in a procedure call. For more information, refer to "Registering Stored Procedures" below and SQLProcedureColumns - Get Input/Output Parameter Information for A Procedure.

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

[ DB2 List of Books | Search the DB2 Books ]