CALL

The XDB Server can run a stored procedure on the mainframe (through XDB Link) or on an XDB Server through use of the CALL statement.
Restriction: This topic applies to Windows environments only.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Authorization

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:

  • For static SQL programs that use the syntax CALL procedure, the owner of the plan or package that contains the CALL statement must have one of the following:
    • The EXECUTE privilege on the stored procedure
    • Ownership of the stored procedure
    • SYSADM authority
  • For static SQL programs that use the syntax CALL host variable (ODBC applications use this form of the CALL statement), the authorization ID of the plan or package that contains the CALL statement must have one of the following:
    • The EXECUTE privilege on the stored procedure
    • Ownership of the stored procedure
    • SYSADM authority

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:

  • The EXECUTE privilege on the package
  • Ownership of the package
  • PACKADM authority for the package's collection
  • SYSADM authority

A PKLIST entry is not required for the stored procedure package.

Syntax

CALL {procedure-name | host-variable}
[({expression | NULL | TABLE transition-table-name}[,...]) |
USING DESCRIPTOR descriptor-name]

Parameters:

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.

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.

procedure-name or host-variable

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:

  • A fully qualified procedure name is a three-part name. The first part is a long identifier that contains the location name that identifies the DBMS at which the procedure is stored. The second part is a short identifier that contains the schema name of the stored procedure. The last part is a long identifier that contains the name of the stored procedure. A period must separate each of the parts. Any or all of the parts can be a delimited identifier.
  • A two-part procedure name has one implicit qualifier. The implicit qualifier is the location name of the current server. The two parts identify the schema name and the name of the stored procedure. A period must separate the two parts.
  • An unqualified procedure name is a one-part name with two implicit qualifiers. The first implicit qualifier is the location name of the current server. The second implicit qualifier depends on the application server. If the server is DB2 for OS/390, the implicit qualifier is the schema name. DB2 uses the SQL path to determine the value of the schema name.
  • If the procedure name is specified as a literal on the CALL statement (CALL procedure-name), the SQL path is the value of the PATH bind option that is associated with the calling package or plan.
  • If a host variable is specified for the procedure name on the CALL statement (CALL host-variable), the SQL path is the value of the CURRENT PATH special register.

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:

  • The user is authorized to call the stored procedure.
  • The number of parameters in the definition of the stored procedure matches the number of parameters specified on the CALL statement.
  • The create timestamp for the stored procedure must be older than the bind timestamp for the package or plan in which the procedure is invoked.

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:

  • It must be a character string variable with a length attribute that is not greater than 255.
  • It must not be followed by an indicator variable.
  • The value of the host variable is a specification that depends on the application server. Regardless of the application server, the specification must:
    • Be left justified within the host variable
    • Not contain embedded blanks
    • Be padded on the right with blanks if its length is less than that of the host variable

In addition, the specification can:

  • Contain upper and lowercase characters. Lowercase characters are not folded to uppercase.
  • Use a delimited identifier for any part of a the three-part procedure name.

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.

Parameters (Expression, Null, Table Transition-Table-Name)

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:

  • IN means as an input value
  • OUT means as an output value
  • INOUT means both as an input and an output value

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.

expression

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:

  • expression can contain references to multiple host variables. expression cannot include a column name or column function or a user-defined function that is sourced on a column function.
  • INOUT or OUT expression can only be a single host variable.

NULL

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.

TABLE transition-table-name

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.

USING DESCRIPTOR descriptor-name

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:

  • SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA. This number must not be less than SQLD. This field is not part of the REXX SQLDA and therefore does not need to be set for REXX programs.
  • SQLDABC to indicate the number of bytes of storage allocated for the SQLDA. This number must be not be less than SQLN*44+16. This field is not part of the REXX SQLDA and therefore does not need to be set for REXX programs.
  • SQLD to indicate the number of variables used in the SQLDA when processing the statement. This number must be the same as the number of parameters of the stored procedure.
  • SQLVAR occurrences to indicate the attributes of the variables.

Variable Usage

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:

  • IN: used only as an input value
  • OUT: used only as an output value
  • INOUT: used as both input and output value
Note:

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.

Example:

The following CALL statement activates a stored procedure named alpha:

EXEC SQL CALL alpha (:P1, :A1, :ED1, :type, :code)