Invokes a procedure stored at the location of a database. A stored procedure, for example, executes at the location of the database, and returns data to the client application.
Programs using the SQL CALL statement are designed to run in two parts, one on the client and the other on the server. The server procedure at the database runs within the same transaction as the client application. If the client application and stored procedure are on the same partition, the stored procedure is executed locally.
Invocation
This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. However, the procedure name may be specified via a host variable and this, coupled with the use of the USING DESCRIPTOR clause, allows both the procedure name and the parameter list to be provided at run time; thus achieving the same effect as a dynamically prepared statement.
Authorization
The authorization rules vary according to the server at which the procedure is stored.
Syntax
>>-CALL----+-procedure-name-+----------------------------------->
'-host-variable--'
>-----+---------------------------------------+----------------><
+-(--+---------------------------+---)--+
| | .-,--------------------. | |
| | V (1) | | |
| '----host-variable--------+-' |
'-USING--DESCRIPTOR--descriptor-name----'
Notes:
Description
If procedure-name is specified it must be an ordinary identifier not greater than 254 bytes. Since this can only be an ordinary identifier, it cannot contain blanks or special characters and the value is converted to upper case. Thus, if it is necessary to use lower case names, blanks or special characters, the name must be specified via a host-variable.
If host-variable is specified, it must be a character-string variable with a length attribute that is not greater than 254 bytes, and it must not include an indicator variable. Note that the value is not converted to upper case. procedure-name must be left-justified.
The procedure name can take one of several forms. The forms supported vary according to the server at which the procedure is stored.
In UNIX-based systems, the DB2 server finds the stored procedure library in the default directory sqllib/function. Unfenced stored procedures are in the sqllib/function/unfenced directory.
In OS/2, the location of the stored procedures is specified by the LIBPATH variable in the CONFIG.SYS file. Unfenced stored procedures are in the sqllib\dll\unfenced directory.
Once the procedure is selected, DB2 will invoke the procedure defined by the external name.
The stored procedure library is located in the directories or specified in the LIBPATH variable, as described in procedure-name.
In a UNIX-based system, for example, if /u/terry/proclib!func was specified, then the stored procedure library proclib would be obtained from the directory /u/terry and the function func from that library would be executed.
In OS/2, if d:\terry\proclib!func was specified, then it would cause the database manager to load the func.dll file from the d:\terry\proclib directory.
In all these cases, the total length of the procedure name including its implicit or explicit full path must not be longer than 254 bytes.
For portability, procedure-name should be specified as a single token no larger than 8 bytes.
Each host-variable is assumed to be used for exchanging data in both directions between client and server. In order to avoid sending unnecessary data between client and server, the client application should provide an indicator variable with each parameter and set the indicator to -1 if the parameter is not used to transmit data to the stored procedure. The stored procedure should set the indicator variable to -128 for any parameter that is not used to return data to the client application.
If the server is DB2 Universal Database the parameters must have matching data types in both the client and server program. 62
Before the CALL statement is processed, the application must set the following fields in the SQLDA:
The following fields of each Secondary SQLVAR element passed must be initialized:
Each SQLDA is assumed to be used for exchanging data in both directions between client and server. In order to avoid sending unnecessary data between client and server, the client application should set the SQLIND field to -1 if the parameter is not used to transmit data to the stored procedure. The stored procedure should set the SQLIND field -128 for any parameter that is not used to return data to the client application.
If the client and server application needs to specify LOB data from an SQLDA, allocate double the number of SQLVAR entries.
LOB data types are supported by stored procedures starting with DB2 Version 2. The LOB data types are not supported by all down level clients or servers.
If an SQL procedure successfully issues a RETURN statement with a status value, this value is returned in the first SQLERRD field of the SQLCA. If the CALL statement is issued in an SQL procedure, use the GET DIAGNOSTICS statement to retrieve the RETURN_STATUS value. The value is -1 if the SQLSTATE indicates an error.
If the client application program is written using CLI, result sets can be returned directly to the client application. The stored procedure indicates that a result set is to be returned by declaring a cursor on that result set, opening a cursor on the result set, and leaving the cursor open when exiting the procedure.
At the end of a procedure that is invoked via CLI:
For additional information refer to the Application Development Guide and the CLI Guide and Reference.
In general, the CALL statement will not work with existing DARI procedures. See the Application Development Guide for details.
The settings of the special registers of the caller are inherited by the stored procedure on invocation and restored upon return to the caller. Special registers may be changed within a stored procedure, but these changes do not effect the caller. This is not true for legacy stored procedures (those defined with parameter style DB2DARI or found in the default library), where the changes made to special registers in a procedure become the settings for the caller.
Examples
Example 1:
In C, invoke a procedure called TEAMWINS in the ACHIEVE library passing it a parameter stored in the host variable HV_ARGUMENT.
strcpy(HV_PROCNAME, "ACHIEVE!TEAMWINS"); CALL :HV_PROCNAME (:HV_ARGUMENT);
Example 2:
In C, invoke a procedure called :SALARY_PROC using the SQLDA named INOUT_SQLDA.
struct sqlda *INOUT_SQLDA; /* Setup code for SQLDA variables goes here */ CALL :SALARY_PROC USING DESCRIPTOR :*INOUT_SQLDA;
Example 3:
A Java stored procedure is defined in the database using the following statement:
CREATE PROCEDURE PARTS_ON_HAND (IN PARTNUM INTEGER,
OUT COST DECIMAL(7,2),
OUT QUANTITY INTEGER)
EXTERNAL NAME 'parts!onhand'
LANGUAGE JAVA PARAMETER STYLE DB2GENERAL;
A Java application calls this stored procedure using the following code fragment:
... CallableStatement stpCall ; String sql = "CALL PARTS_ON_HAND ( ?,?,? )" ; stpCall = con.prepareCall( sql ) ; /* con is the connection */ stpCall.setInt( 1, variable1 ) ; stpCall.setBigDecimal( 2, variable2 ) ; stpCall.setInt( 3, variable3 ) ; stpCall.registerOutParameter( 2, Types.DECIMAL, 2 ) ; stpCall.registerOutParameter( 3, Types.INTEGER ) ; stpCall.execute() ; variable2 = stpCall.getBigDecimal(2) ; variable3 = stpCall.getInt(3) ; ...
This application code fragment will invoke the Java method onhand in class parts since the procedure-name specified on the CALL statement is found in the database and has the external name 'parts!onhand'.