This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.
The privileges required to execute the CALL statement are determined by the owner of the package or plan that contains the CALL statement. If the server is DB2 for MVS/ESA (or an XDB Server), that AuthID must have either the EXECUTE privilege on the package associated with the stored procedure, ownership of the package associated with the stored procedure, PACKADM authority for the package's collection, or SYSADM authority.
The authorization ID that must have the EXECUTE privilege on the stored procedure depends on the form of the CALL statement:
The authorization that is required to run the stored procedure package and any packages that are used under the stored procedure apply to any form of the CALL statement as follows:
Stored procedure package
One of the authorization IDs that are defined below under Set of authorization IDs must have at least one of the following on the stored procedure package:
A PKLIST entry is not required for the stored procedure package.
CALL {procedure-name | host-variable}
[({expression | NULL | TABLE transition-table-name}[,...]) |
USING DESCRIPTOR descriptor-name]
| procedure-name | A qualified or unqualified name that identifies the stored procedure. Procedure names can be three-part, two-part or unqualified one-part names. A fully qualified procedure name begins with a location name that identifies the DBMS at which the procedure is stored. The second and third parts identify the stored procedure. A period must separate each of the parts. |
| host-variable-1 | Character string variable with a maximum length of 254 bytes that identifies the stored procedure by the value of the specified host variable. This host variable must be preceded by a colon and must not be followed by an indicator variable. |
| host-variable-2 | Identifies a parameter of the CALL statement. The host variable must identify a host variable (not a structure) described in the program according to the rules for declaring host variables. The data type of the variable must be compatible with the data type of the corresponding parameter of the stored procedure. |
| constant | A parameter of the stored procedure expressed as a constant in the CALL statement. The data type of the constant must be compatible with the corresponding parameter of the stored procedure and that parameter must be defined as IN. |
| NULL | A parameter of the stored procedure expressed as a NULL value in the CALL statement. The corresponding parameter of the stored procedure must be defined as IN, and the description of the stored procedure must allow for null parameters. |
| descriptor-name | Identifies an SQLDA that must contain a valid description of host variables. In C, the descriptor-name can be a pointer to an SQLDA. |
Identifies the stored procedure to call. The procedure name can be specified as a character string constant or within a host variable.
A procedure name is a qualified or unqualified name. Each part of the name must be composed of SBCS characters:
DB2 searches the schema names in the SQL path from left to right until a stored procedure with the specified schema name is found in the DB2 catalog. When a matching schema.procedure-name is found, the search stops only if the following conditions are true:
If the list of schemas in the SQL path is exhausted before the procedure name is resolved, an error is returned.
If a host variable is used:
In addition, the specification can:
When the CALL statement is executed, the procedure name or specification must identify a stored procedure that exists at the application server.
When the package that contains the CALL statement is bound, the stored procedure that is invoked must be created if VALIDATE(BIND) is Specified. Although the stored procedure does not need to be created at bind time if VALIDATE(RUN) is specified, it must be created when the CALL statement is executed.
Identifies a list of values to be passed as parameters to the stored procedure. If USING DESCRIPTOR is specified, each host variable described by the identified SQLDA is a parameter of the CALL statement. If host structures are not specified in the CALL statement, the nth parameter of the CALL statement corresponds to the nth parameter in the stored procedure, and the number of parameters in each must be the same. Otherwise, each reference to a host structure is replaced by a reference to each of the variables contained in that host structure, and the resulting number of parameters must be the same as the number of parameters defined for the stored procedure.
Each parameter of a stored procedure is described at the server. In addition to attributes such as data type and length, the description of each parameter indicates how the stored procedure uses it:
When the CALL statement is executed, the value of each of its parameters is assigned to the corresponding parameter of the stored procedure. In cases where the parameters of the CALL statement are not an exact match to the data types of the parameters of the stored procedure, each parameter specified in the CALL statement is converted to the data type of the corresponding parameter of the stored procedure at execution. The conversion occurs according to the same rules as assignment to columns.
Conversion can occur when precision, scale, length, encoding scheme, or CCSID differ between the parameter specified in the CALL statement and the data type of the corresponding parameter of the stored procedure. Conversion might occur for a character string parameter specified in the CALL statement when the corresponding parameter of the stored procedure has a different encoding scheme or CCSID. For example, an error occurs when the CALL statement passes mixed data that actually contains DBCS characters as input to a parameter of the stored procedure that is declared with an SBCS subtype. Likewise, an error occurs when the stored procedure returns mixed data that actually contains DBCS characters in the parameter of the CALL statement that has an SBCS subtype.
The parameter is the result of the specified expression, which is evaluated before the stored procedure is invoked.
If expression is a single host variable, the corresponding parameter of the procedure can be defined as IN, INOUT, or OUT. Otherwise, the corresponding parameter of the procedure must be defined as IN. In addition, the host variable can identify a structure. Any host variable or structure that is specified must be described in the application program according to the rules for declaring host structures and variables. A reference to a host structure is replaced by a reference to each of the variables contained in the host structure.
If the result of the expression can be the null value, either the description of the procedure must allow for null parameters or the corresponding parameter of the procedure must be defined as OUT.
The following additional rules apply depending on how the corresponding parameter was defined in the CREATE Procedure statement for the procedure:
The parameter is a null value. The corresponding parameter of the procedure must be defined as IN and the description of the procedure must allow for null parameters.
The parameter is a transition table and it is passed to the procedure as a table locator. . You can use the CALL statement with the TABLE clause only within the definition of the triggered action of a trigger. The name of a transition table must be specified in the CALL statement if the corresponding parameter of the procedure was defined in the TABLE LIKE clause of the CREATE PROCEDURE statement.
The XDB Server operating environment does not currently provide this functionality.
Identifies an SQLDA that contains a valid description of the host variables that are to be passed as parameters to the stored procedure. If the stored procedure has no parameters, an SQLDA is ignored.
Before the CALL statement is processed, the user must set the following fields in the SQLDA:
Each specification of a host-variable, constant or NULL is a parameter of the CALL statement. If USING DESCRIPTOR is specified, each host variable described by the identified SQLDA is a parameter of the CALL statement. The nth parameter of the CALL statement corresponds to the nth parameter of the stored procedure. When the CALL statement is executed, the number of parameters of the stored procedure and each pair of corresponding parameters must be consistent.
A description of each parameter describes how that parameter is used by the stored procedure. In addition to parameter attributes such as data type and length, the following information on each parameter is also stored in SYSIBM.SYSPROCEDURES:
When a trigger issues a CALL statement to invoke a stored procedure, the parameters that are specified in the CALL statement cannot be host variables and the USING DESCRIPTOR clause cannot be specified.
A program that is executing as a stored procedure, a user-defined function, or a trigger can issue a CALL statement. When a stored procedure, user-defined function, or trigger calls a stored procedure, user-defined function, or trigger, the call is considered to be nested. Stored procedures, user-defined functions, and triggers can be nested up to 16 levels deep on a single system. Nesting can occur within a single DB2 subsystem or when a stored procedure or user-defined function is invoked at a remote server.
If a stored procedure returns any query result sets, the result sets are returned to the caller of the stored procedure. If the SQL CALL statement is nested, the result sets are visible only to the program that is at the previous nesting level.
Some stored procedures cannot be nested. A stored procedure, user-defined function, or trigger cannot call a stored procedure that is defined with the COMMIT ON RETURN attribute. A stored procedure can call another stored procedure only if they execute in the same type of address space; they must both execute in a DB2-established address space or in a WLM-established address space.
The following CALL statement activates a stored procedure named alpha:
EXEC SQL CALL alpha (:P1, :A1, :ED1, :type, :code)
Description
A stored procedure is invoked by the CALL statement, which passes a list of parameters to the procedure. When the CALL statement is executed, the procedure name specified must identify a stored procedure that exists at the application server. If the server is DB2 for MVS/ESA (or an XDB Server), the last or only part of the name must be equal to some value of the NAME column of the SYSIBM.SYSROUTINES catalog table.
DB2 and the XDB Server use the information stored in SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS to automatically activate a stored procedure definition, which includes identification of the parameter list used to implement the stored procedure. The parameters issued in your procedure's CALL statement must correspond to the parameters defined in SYSIBM.SYSPARMS.