Calling a stored procedure from your application

To run a stored procedure, you can either call it from a client program or invoke it from the Db2 command line processor.

Before you begin

Before you call a stored procedure, ensure that you have all of the following authorizations that are required to run the stored procedure:

Authorization to execute the stored procedure that is referenced in the CALL statement.
The authorizations that you need depend on whether the form of the CALL statement is CALL procedure-name or CALL :host-variable.
Authorization to execute any triggers or user-defined functions that the stored procedure invokes.
Authorization to execute the stored procedure package and any packages under the stored procedure package.
For example, if the stored procedure invokes any user-defined functions, you need authorization to execute the packages for those user-defined functions.

About this task

An application program that calls a stored procedure can perform one or more of the following actions:

Call more than one stored procedure.
Call a single stored procedure more than once at the same or at different levels of nesting. However, do not assume that the variables for the stored procedures persist between calls.
If a stored procedure runs as a main program, before each call, Language Environment® reinitializes the storage that is used by the stored procedure. Program variables for the stored procedure do not persist between calls.
If a stored procedure runs as a subprogram, Language Environment does not initialize the storage between calls. Program variables for the stored procedure can persist between calls. However, you should not assume that your program variables are available from one stored procedure call to another call for the following reasons:
- Stored procedures from other users can run in an instance of Language Environment between two executions of your stored procedure.
- Consecutive executions of a stored procedure might run in different stored procedure address spaces.
- The z/OS® operator might refresh Language Environment between two executions of your stored procedure.
Call a local or remote stored procedure.
If both the client and server application environments support two-phase commit, the coordinator controls updates between the application, the server, and the stored procedures. If either side does not support two-phase commit, updates fail.
Mix CALL statements with other SQL statements.
Use any of the Db2 attachment facilities.

Db2 runs stored procedures under the Db2 thread of the calling application, which means that the stored procedures are part of the caller's unit of work.

JDBC and ODBC applications: These instructions do not apply to JDBC and ODBC applications. Instead, see the following information for how to call stored procedures from those applications:

For ODBC applications, see Stored procedure calls in a Db2 ODBC application.
For JDBC applications, see Calling stored procedures in JDBC applications

Procedure

To call a stored procedure from your application:

Assign values to the IN and INOUT parameters.
Optional: To improve application performance, initialize the length of LOB output parameters to zero.
If the stored procedure exists at a remote location, perform the following actions:
1. Assign values to the OUT parameters.
  When you call a stored procedure at a remote location, the local Db2 server cannot determine whether the parameters are input (IN) or output (OUT or INOUT) parameters. Therefore, you must initialize the values of all output parameters before you call a stored procedure at a remote location.
2. Optional: Issue an explicit CONNECT statement to connect to the remote server.
  If you do not issue this statement explicitly, you can implicitly connect to the server by using a three-part name to identify the stored procedure in the next step.
  
  The advantage of issuing an explicit CONNECT statement is that your CALL statement, which is described in the next step, is portable to other operating systems. The advantage of implicitly connecting is that you do not need to issue this extra CONNECT statement.
  
  Requirement: When deciding whether to implicitly or explicitly connect to the remote server, consider the requirement for programs that execute the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE statements. You must use the same form of the procedure name on the CALL statement and on the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE statement.
Invoke the stored procedure with the SQL CALL statement. Make sure that you pass parameter data types that are compatible.
If the stored procedure exists on a remote server and you did not issue an explicit CONNECT statement, specify a three-part name to identify the stored procedure, and implicitly connect to the server where the stored procedure is located.

For native SQL procedures, the active version of the stored procedure is invoked by default. Optionally, you can specify a version of the stored procedure other than the active version.

To allow null values for parameters, use indicator variables.
Optional: Retrieve the status of the procedure.
Process any output, including the OUT and INOUT parameters.
If the stored procedure returns multiple result sets, retrieve those result sets.
Recommendation: Close the result sets after you retrieve them, and issue frequent commits to prevent Db2 storage shortages and EDM POOL FULL conditions.
For PL/I applications, also perform the following actions:
1. Include the run time option NOEXECOPS in your source code.
2. Specify the compile-time option SYSTEM(MVS).
These additional steps ensure that the linkage conventions work correctly on z/OS.
For C applications, include the following line in your source code:
```
#pragma runopts(PLIST(OS))
```
This code ensures that the linkage conventions work correctly on z/OS.

This option is not applicable to other operating systems. If you plan to use a C stored procedure on other platforms besides z/OS, use one of the forms of conditional compilation, as shown in the following example, to include this option only when you compile on z/OS.
Form 1
```
#ifdef MVS
 #pragma runopts(PLIST(OS))
#endif
```
Form 2
```
#ifndef WKSTN
 #pragma runopts(PLIST(OS))
#endif
```
Prepare the application as you would any other application by precompiling, compiling, and link-editing the application and binding the DBRM.
If the application calls a remote stored procedure, perform the following additional steps when you bind the DBRM:
- Bind the DBRM into a package at the local Db2 server. Use the bind option DBPROTOCOL(DRDA). If the stored procedure name cannot be resolved until run time, also specify the bind option VALIDATE(RUN). The stored procedure name might not be resolved at run time if you use a variable for the stored procedure name or if the stored procedure exists on a remote server.
- Bind the DBRM into a package at the remote Db2 server. If your client program accesses multiple servers, bind the program at each server.
- Bind all packages into a plan at the local Db2 server. Use the bind option DBPROTOCOL(DRDA).
Ensure that stored procedure completed successfully.
If a stored procedure abnormally terminates, Db2 performs the following actions:
- The calling program receives an SQL error as notification that the stored procedure failed.
- Db2 places the calling program's unit of work in a must-rollback state.
- Db2 stops the stored procedure, and subsequent calls fail, in either of the following conditions:
  - The number of abnormal terminations equals the STOP AFTER n FAILURES value for the stored procedure.
  - The number of abnormal terminations equals the default MAX ABEND COUNT value for the subsystem.
- The stored procedure does not handle the abend condition, and Db2 refreshes the environment for Language Environment to recover the storage that the application uses. In most cases, the environment does not need to restart.
- A data set is allocated in the DD statement CEEDUMP in the JCL procedure that starts the stored procedures address space. In this case, Language Environment writes a small diagnostic dump to this data set. Use the information in the dump to debug the stored procedure.
- In a data sharing environment, the stored procedure is placed in STOPABN status only on the member where the abends occurred. A calling program can invoke the stored procedure from other members of the data sharing group. The status on all other members is STARTED.

Examples

Example 1: Simple CALL statement

The following example shows a simple CALL statement that you might use to invoke stored procedure A:

EXEC SQL CALL A (:EMP, :PRJ, :ACT, :EMT, :EMS, :EME, :TYPE, :CODE);

In this example, :EMP, :PRJ, :ACT, :EMT, :EMS, :EME, :TYPE, and :CODE are host variables that you have declared earlier in your application program.

Example 2: Using a host structure for multiple parameter values

Instead of passing each parameter separately, as shown in the example of a simple CALL statement, you can pass them together as a host structure. For example, assume that you defined the following host structure in your application:

struct {
  char EMP[7];
  char PRJ[7];
  short ACT;
  short EMT;
  char  EMS[11];
  char  EME[11];
} empstruc;

You can then issue the following CALL statement to invoke stored procedure A:

EXEC SQL CALL A (:empstruc, :TYPE, :CODE);

Example 3: Calling a remote stored procedure

The following example shows how to explicitly connect to LOCA and then issue a CALL statement:

EXEC SQL CONNECT TO LOCA;
EXEC SQL CALL SCHEMAA.A (:EMP, :PRJ, :ACT, :EMT, :EMS, :EME,
  :TYPE, :CODE);

The following example shows how to implicitly connect to LOCA by specifying the three-part name for stored procedure A in the CALL statement:
```
EXEC SQL CALL LOCA.SCHEMAA.A (:EMP, :PRJ, :ACT, :EMT, :EMS,
  :EME, :TYPE, :CODE);
```

Example 4: Passing parameters that can have null values

The preceding examples assume that none of the input parameters can have null values. The following example shows how to allow for null values for the parameters by passing indicator variables in the parameter list:

EXEC SQL CALL A (:EMP :IEMP, :PRJ :IPRJ, :ACT :IACT,
                 :EMT :IEMT, :EMS :IEMS, :EME :IEME,
                 :TYPE :ITYPE, :CODE :ICODE);

In this example, :IEMP, :IPRJ, :IACT, :IEMT, :IEMS, :IEME, :ITYPE, and :ICODE are indicator variables for the parameters.

Example 5: Passing string constants and null values

The following example CALL statement passes integer and character string constants, a null value, and several host variables:

EXEC SQL CALL A ('000130', 'IF1000', 90, 1.0, NULL, '2009-10-01',
                 :TYPE, :CODE);

Example 6: of using a host variable for the stored procedure name

The following example CALL statement uses a host variable for the name of the stored procedure:

EXEC SQL CALL :procnm (:EMP, :PRJ, :ACT, :EMT, :EMS, :EME,
  :TYPE, :CODE);

Assume that the stored procedure name is A. The host variable procnm is a character variable of length 255 or less that contains the value 'A'. Use this technique if you do not know in advance the name of the stored procedure, but you do know the parameter list convention.

Example 7: Using an SQLDA to pass parameters in a single structure

The following example CALL statement shows how to pass parameters in a single structure, the SQLDA, rather than as separate host variables:

EXEC SQL CALL A USING DESCRIPTOR :sqlda;

sqlda is the name of an SQLDA.

One advantage of using an SQLDA is that you can change the encoding scheme of the stored procedure parameter values. For example, if the subsystem on which the stored procedure runs has an EBCDIC encoding scheme, and you want to retrieve data in ASCII CCSID 437, you can specify the CCSIDs for the output parameters in the SQLVAR fields of the SQLDA.

This technique for overriding the CCSIDs of parameters is the same as the technique for overriding the CCSIDs of variables. This technique involves including dynamic SQL for varying-list SELECT statements in your program. When you use this technique, the defined encoding scheme of the parameter must be different from the encoding scheme that you specify in the SQLDA. Otherwise, no conversion occurs.

The defined encoding scheme for the parameter is the encoding scheme that you specify in the CREATE PROCEDURE statement. If you do not specify an encoding scheme in this statement, the defined encoding scheme for the parameter is the default encoding scheme for the subsystem.

Example 8: Reusable CALL statement

Because the following example CALL statement uses a host variable name for the stored procedure and an SQLDA for the parameter list, it can be reused to call different stored procedures with different parameter lists:

EXEC SQL CALL :procnm USING DESCRIPTOR :sqlda;

Your client program must assign a stored procedure name to the host variable procnm and load the SQLDA with the parameter information before issuing the SQL CALL statement.