The CALL statement calls a procedure.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
If a global variable is referenced as an IN or
INOUT parameter, the privileges held by the authorization ID for the
statement must include:
If a global variable is referenced as an OUT
or INOUT parameter, the privileges held by the authorization ID for
the statement must include:
For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure.
>>-CALL--+-procedure-name-+-------------------------------------> '-variable-------' >--+-----------------------------------+----------------------->< +-(--argument-list--)---------------+ +-SQL-descriptors-------------------+ '-USING DESCRIPTOR--descriptor-name-' argument-list |--(--+--------------------+--)---------------------------------| | .-,--------------. | | V | | '---+-expression-+-+-' '-NULL-------' SQL-descriptors |--+------------------------------------------------------------+--> | .-SQL-. .-LOCAL--. | '-INTO--+-----+--DESCRIPTOR--+--------+--SQL-descriptor-name-' '-GLOBAL-' >--+-----------------------------------------------------------------+--| | (1) | | .-SQL-----. .-LOCAL--. | '-USING--+---------+--DESCRIPTOR--+--------+--SQL-descriptor-name-' '-GLOBAL-'
If a variable is specified:
It must be a character-string variable or Unicode graphic-string.
It cannot be a global variable.
If the procedure name is unqualified, it is implicitly qualified based on the path and number of parameters. For more information see Qualification of unqualified object names.
If the procedure-name identifies a procedure that was defined by a DECLARE PROCEDURE statement, and the current server is a DB2® for i product, then:
Otherwise:
If the current server is a DB2 for i, the attributes of the parameters will be the same as the attributes of the arguments specified on the CALL statement. 1
argument-list
Each parameter defined (using a CREATE PROCEDURE or DECLARE PROCEDURE statement) as OUT or INOUT must be specified as a variable.
The number of arguments specified must be the same as the number of parameters of a procedure defined at the current server with the specified procedure-name.
The application requester assumes all parameters that are variables are INOUT parameters except for Java, where it is assumed all parameters that are variables are IN unless the mode is explicitly specified in the variable reference. All parameters that are not variables are assumed to be input parameters. The actual attributes of the parameters are determined by the current server.
expression
An expression of
the type described in Expressions, that
does not include an aggregate function or column name. If extended
indicator variables are enabled, the extended indicator variable values
of DEFAULT and UNASSIGNED must not be used for that expression.
See GET DESCRIPTOR for an explanation of the information
that is placed in the SQL descriptor.
See SET DESCRIPTOR for an explanation of the information
in the SQL descriptor.
SQLD must be set to a value greater than or equal to zero and less than or equal to SQLN. It must be the same as the number of parameters in the CALL statement. The nth variable described by the SQLDA corresponds to the nth parameter marker in the prepared statement. (For a description of an SQLDA, see SQLDA (SQL descriptor area).)
Note that RPG/400® does not provide the function for setting pointers. Because the SQLDA uses pointers to locate the appropriate variables, you have to set these pointers outside your RPG/400 application.
The USING DESCRIPTOR clause is not supported for a CALL statement within a Java program.
Parameter
assignments: When the CALL statement is executed, the value of
each of its parameters is assigned (using storage assignment rules)
to the corresponding parameter of the procedure. Control is passed
to the procedure according to the calling conventions of the host
language. When execution of the procedure is complete, the value
of each parameter of the procedure is assigned (using
storage
assignment rules for SQL parameters, otherwise using
retrieval
assignment rules) to the corresponding parameter of the CALL statement
defined as OUT or INOUT. For details on the assignment rules, see Assignments and comparisons.
Global variables:
A
global variable may be specified and will be modified if used as a
parameter that is INOUT or OUT.
Cursors and prepared statements in procedures: All cursors opened in the called procedure that are not result set cursors are closed, and all statements prepared in the called procedure are destroyed when the procedure ends. CLOSQLCSR(*ENDACTGRP) can be used to keep cursors open when the procedure ends. For more information, see SET OPTION.
When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.
Locks in procedures: All locks that have been acquired in the called procedure are retained until the end of the unit of work.
Errors from procedures: A procedure can return errors (or warnings) using the SQLSTATE like other SQL statements. Applications should be aware of the possible SQLSTATEs that can be expected when invoking a procedure. The possible SQLSTATEs depend on how the procedure is coded. Procedures may also return SQLSTATEs such as those that begin with '38' or '39' if the database manager encounters problems executing the procedure. Applications should therefore be prepared to handle any error SQLSTATE that may result from issuing a CALL statement.
Nesting CALL statements: A program that is executing as a procedure, a user-defined function, or a trigger can issue a CALL statement. When a procedure, user-defined function, or trigger calls a procedure, user-defined function, or trigger, the call is considered to be nested. There is no limit on how many levels procedures and functions can be nested, but triggers can only be nested up to 200 levels deep.
If a procedure returns any query result sets, the result sets are returned to the caller of the procedure. If the SQL CALL statement is nested, the result sets are visible only to the program that is at the previous nesting level. For example, if a client program calls procedure PROCA, which in turn calls procedure PROCB. Only PROCA can access any result sets that PROCB returns; the client program has no access to the query result sets.
When an SQL or an external procedure is called, an attribute is set for SQL data-access that was defined when the procedure was created. The possible values for the attribute are:
NONE
CONTAINS
READS
MODIFIES
If a second procedure is invoked within the execution of the current procedure, an error is issued if:
REXX procedures: If the external procedure to be called is a REXX procedure, then the procedure must be declared using the CREATE PROCEDURE or DECLARE PROCEDURE statement.
Variables cannot be used in the CALL statement within a REXX procedure. Instead, the CALL must be the object of a PREPARE and EXECUTE using parameter markers.
Multiple SQL descriptors on CALL: If SQL descriptors are specified on CALL and a procedure has IN or INOUT parameters and OUT or INOUT parameters, two descriptors must be specified. The number of variables that must be allocated in the SQL descriptors depends on how the SQL descriptor attributes are set and the number of each type of parameter.
This is the recommended technique for specifying multiple SQL descriptors on a CALL statement.
If multiple SQL descriptors are specified, the DATA or INDICATOR items associated with any INOUT parameters in the input SQL descriptor may also be modified when the procedure is called. Thus, a SET DESCRIPTOR may be necessary to reset the DATA and INDICATOR items for such an input SQL descriptor prior to its use in another SQL statement.
Example 1: Call procedure PGM1 and pass two parameters.
CALL PGM1 (:hv1,:hv2)
Example 2: In C, invoke a procedure called SALARY_PROCED 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 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) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'parts!onhand';
A Java application calls this procedure on the connection context 'ctx' using the following code fragment:
...
int variable1;
BigDecimal variable2;
Integer variable3;
...
#sql [ctx] {CALL PARTS_ON_HAND(:IN variable1, :OUT variable2, :OUT variable3)};
...
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'.

Example 4: Call procedure PGM2 on relational database BRANCHRDB2 and pass one parameter.
CALL BRANCHRDB2.SCHEMA3.PGM2 (:hv1)
