DB2 Version 9.7 for Linux, UNIX, and Windows

CALL statement

The CALL statement calls a procedure or a foreign procedure.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. When invoked using the command line processor, there are some additional rules for specifying arguments of the procedure. For more information, refer to "Using command line SQL statements and XQuery statements".

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

EXECUTE privilege on the procedure
DATAACCESS authority

If a matching procedure exists that the authorization ID of the statement is not authorized to execute, an error is returned (SQLSTATE 42501).

Syntax


>>-CALL--procedure-name--+----------------------------+--------><
                         |    .-,----------------.    |   
                         |    V                  |    |   
                         '-(------| argument |---+--)-'   

argument

|--+--------------------+--+-expression-+-----------------------|
   '-parameter-name--=>-'  +-DEFAULT----+   
                           '-NULL-------'

Description

procedure-name

Specifies the procedure that is to be called. It must be a procedure that is described in the catalog. The specific procedure to invoke is chosen using procedure resolution. (For more details, see the "Notes" section of this statement.)

argument

parameter-name

Name of the parameter to which the argument is assigned. When an argument is assigned to a parameter by name, then all the arguments that follow it must also be assigned by name (SQLSTATE 4274K).

A named argument must be specified only once (implicitly or explicitly) (SQLSTATE 4274K).

Named arguments are not supported on the call to an uncataloged procedure (SQLSTATE 4274K).

expression or DEFAULT or NULL

Each specification of expression, the DEFAULT keyword, or the NULL keyword is an argument of the CALL. The nth unnamed argument of the CALL statement corresponds to the nth parameter defined in the CREATE PROCEDURE statement for the procedure.

Named arguments correspond to the same named parameter, regardless of the order in which they are specified.

If the DEFAULT keyword is specified, the default as defined in the CREATE PROCEDURE statement is used if it exists; otherwise the null value is used as the default.

If the NULL keyword is specified, the null value is passed as the parameter value.

Each argument of the CALL must be compatible with the corresponding parameter in the procedure definition as follows:

IN parameter
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the storage assignment rules.
OUT parameter
- The argument must be a single variable or parameter marker (SQLSTATE 42886).
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the retrieval assignment rules.
INOUT parameter
- The argument must be a single variable or parameter marker (SQLSTATE 42886).
- The argument must be assignable to the parameter.
- The assignment of a string argument uses the storage assignment rules on invocation and the retrieval assignment rules on return.

Rules

When invoking a procedure, arguments must be specified for all parameters that are not defined to have a default value (SQLSTATE 428HF).

Notes

Parameter assignments: When the CALL statement is executed, the value of each of its arguments is assigned (using storage assignment) to the corresponding parameter of the procedure. A parameter value that is defined to have a default value can be omitted from the argument list when invoking the procedure.

When the CALL statement is executed, 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) to the corresponding argument of the CALL statement defined as OUT or INOUT. If an error is returned by the procedure, OUT arguments are undefined and INOUT arguments are unchanged. For details on the assignment rules, see "Assignments and comparisons".

When the CALL statement is in an SQL procedure and is calling another SQL procedure, assignment of XML parameters is done by reference. When an XML argument is passed by reference, the input node trees, if any, are used directly from the XML argument, preserving all properties, including document order, the original node identities, and all parent properties.

Procedure signatures: A procedure is identified by its schema, a procedure name, and the number of parameters. This is called a procedure signature, which must be unique within the database. There can be more than one procedure with the same name in a schema, provided that the number of parameters is different for each procedure.

SQL path: A procedure can be invoked by referring to a qualified name (schema and procedure name), followed by an optional list of arguments enclosed by parentheses. A procedure can also be invoked without the schema name, resulting in a choice of possible procedures in different schemas with the same number of parameters. In this case, the SQL path is used to assist in procedure resolution. The SQL path is a list of schemas that is searched to identify a procedure with the same name and number of parameters. For static CALL statements, SQL path is specified using the FUNCPATH bind option. For dynamic CALL statements, SQL path is the value of the CURRENT PATH special register.

Procedure resolution: Given a procedure invocation, the database manager must decide which of the possible procedures with the same name to execute.

Let A be the number of arguments in a procedure invocation.
Let P be the number of parameters in a procedure signature.
Let N be the number of parameters without a default.

Candidate procedures for resolution of a procedure invocation are selected based on the following criteria:

Each candidate procedure has a matching name and an applicable number of parameters. An applicable number of parameters satisfies the condition N ≤ A ≤ P.
Each candidate procedure has parameters such that for each named argument in the CALL statement there exists a parameter with a matching name that does not already correspond to a positional (or unnamed) argument.
Each parameter of a candidate procedure that does not have a corresponding argument in the CALL statement, specified by either position or name, is defined with a default.
Each candidate procedure from a set of one or more schemas has the EXECUTE privilege associated with the authorization ID of the statement invoking the function.

In addition, the set of candidate procedures depends on the environment where the procedure is invoked and how the procedure name is qualified.

If the procedure name is unqualified, procedure resolution is done using the steps that follow:
1. If an unqualified procedure is invoked from within a module object, search within the module for candidate procedures. If one or more candidate procedures are found in the context module, then these candidate procedures are included with any candidate procedures from the schemas in the SQL path (see next item).
2. Search all schema procedures with a schema in the SQL path for candidate procedures. If one or more candidate procedures are found in the schemas of the SQL path, then these candidate procedures are included with any candidate procedures from the context module (see previous item). If a single candidate procedure remains, resolution is complete. If there are multiple candidate procedures, choose the procedure from the context module if still a candidate and otherwise choose the procedure whose schema is earliest in the SQL path. If there are still multiple candidate procedures, determine the candidate procedure with the lowest number of parameters and eliminate candidate procedures with a higher number of parameters.
If there are no candidate procedures remaining after step 2, an error is returned (SQLSTATE 42884).
If the procedure name is qualified, procedure resolution is done using the steps that follow:
1. If the procedure is invoked from within a module and the qualifier matches the name of the module from within which the procedure is invoked, search within the module for candidate procedures. If the qualifier is a single identifier, then the schema name of the module is ignored when matching the module name. If the qualifier is a two part identifier, then it is compared to the schema-qualified module name when determining a match. If a single candidate procedure exists, resolution is complete. If there are multiple candidate procedures, choose the candidate procedure with the least number of parameters. If the qualifier does not match or there are no candidate procedures, then continue with the next step.
2. Consider the qualifier as a schema name and search within that schema for candidate procedures. If a single candidate procedure exists, resolution is complete. If there are multiple candidate procedures, choose the candidate procedure with the least number of parameters and resolution is complete. If the schema does not exist or there are no authorized candidate procedures, and the qualifier matched the name of the module in the first step, then return an error. Otherwise, continue to the next step.
3. Consider the qualifier as a module name, without considering EXECUTE privilege on modules.
  - If the module name is qualified with a schema name, then search published procedures within this module for candidate procedures.
  - If the module name is not qualified with a schema name, then the schema for the module is the first schema in the SQL path that has a matching module name. If found, then search published procedures within this module for candidate procedures.
  - If the module is not found using the SQL path, check for a module public alias that matches the name of the procedure qualifier. If found, then search published procedures within this module for candidate procedures.
  If a matching module is not found or there are no candidate procedures in the matching module, then a procedure not found error is returned (SQLSTATE 42884). If there are multiple candidate procedures, choose the candidate procedure with the least number of parameters. Resolution is complete if the authorization ID of the CALL statement has EXECUTE privilege on the module of the remaining candidate procedure, otherwise an authorization error is returned (SQLSTATE 42501).

Retrieving the DB2_RETURN_STATUS from an SQL procedure: 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 DB2_RETURN_STATUS value. The value is -1 if the SQLSTATE indicates an error. The values is 0 if no error is returned and the RETURN statement was not specified in the procedure.

Returning result sets from procedures: If the calling program is written using CLI, JDBC, or SQLJ, or the caller is an SQL procedure, result sets can be returned directly to the caller. The 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:

For every cursor that has been left open, a result set is returned to the caller or (for WITH RETURN TO CLIENT cursors) directly to the client.
Only unread rows are passed back. For example, if the result set of a cursor has 500 rows, and 150 of those rows have been read by the procedure at the time the procedure is terminated, rows 151 through 500 will be returned to the caller or application (as appropriate).

If the procedure was invoked from CLI or JDBC, and more than one cursor is left open, the result sets can only be processed in the order in which the cursors were opened.

Improving performance: The values of all arguments are passed from the application to the procedure. To improve the performance of this operation, host variables that correspond to OUT parameters and have lengths of more than a few bytes should be set to the null value before the CALL statement is executed.

Nesting CALL statements: Procedures can be called from routines as well as application programs. When a procedure is called from a routine, the call is considered to be nested.

If a procedure returns any query result sets, the result sets are returned as follows:

RETURN TO CALLER result sets are visible only to the program that is at the previous nesting level.
RETURN TO CLIENT results sets are visible only if the procedure was invoked from a set of nested procedures. If a function or method occurs anywhere in the call chain, the result set is not visible. If the result set is visible, it is only visible to the client application that made the initial procedure call.

Consider the following example:

  Client program:
  EXEC SQL CALL PROCA;

    PROCA:
    EXEC SQL CALL PROCB;

      PROCB:
      EXEC SQL DECLARE B1 CURSOR WITH RETURN TO CLIENT ...;
      EXEC SQL DECLARE B2 CURSOR WITH RETURN TO CALLER ...;
      EXEC SQL DECLARE B3 CURSOR FOR SELECT UDFA FROM T1;

        UDFA:
        EXEC SQL CALL PROCC;

          PROCC:
          EXEC SQL DECLARE C1 CURSOR WITH RETURN TO CLIENT ...;
          EXEC SQL DECLARE C2 CURSOR WITH RETURN TO CALLER ...;

From procedure PROCB:

Cursor B1 is visible in the client application, but not visible in procedure PROCA.
Cursor B2 is visible in PROCA, but not visible to the client.

From procedure PROCC:

Cursor C1 is visible to neither UDFA nor to the client application. (Because UDFA appears in the call chain between the client and PROCC, the result set is not returned to the client.)
Cursor C2 is visible in UDFA, but not visible to any of the higher procedures.

Nesting procedures within triggers, compound statements, functions, or methods: When a procedure is called within a trigger, compound statement, function, or method:

The procedure must not issue a COMMIT or a ROLLBACK statement.
Result sets returned from the procedure cannot be accessed.
If the procedure is defined as READS SQL DATA or MODIFIES SQL DATA, no statement in the procedure can access a table that is being modified by the statement that invoked the procedure (SQLSTATE 57053). If the procedure is defined as MODIFIES SQL DATA, no statement in the procedure can modify a table that is being read or modified by the statement that invoked the procedure (SQLSTATE 57053).

When a procedure is called within a function or method:

The procedure has the same table access restrictions as the invoking function or method.
Savepoints defined before the function or method was invoked will not be visible to the procedure, and savepoints defined inside the procedure will not be visible outside the function or method.
RETURN TO CLIENT result sets returned from the procedure cannot be accessed from the client.

Compilation of CALL statements from DB2® for i and DB2 for z/OS®: The compilation of CALL statements from DB2 for i and DB2 for z/OS implicitly behave as if CALL_RESOLUTION DEFERRED was specified. When CALL statements are compiled with CALL_RESOLUTION DEFERRED, all arguments must be provided via host variables, and expressions are not allowed.

Syntax alternatives: There is an older form of the CALL statement that can be embedded in an application by precompiling the application with the CALL_RESOLUTION DEFERRED option. This option is not available for SQL procedures and federated procedures.

Examples

Example 1: 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)
                   EXTERNAL NAME 'parts!onhand'
                   LANGUAGE JAVA
                   PARAMETER STYLE DB2GENERAL;

A Java application calls this 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, hvPartnum);
   stpCall.setBigDecimal(2, hvCost);
   stpCall.setInt(3, hvQuantity);

   stpCall.registerOutParameter(2, Types.DECIMAL, 2);
   stpCall.registerOutParameter(3, Types.INTEGER);

   stpCall.execute();

   hvCost = stpCall.getBigDecimal(2);
   hvQuantity = stpCall.getInt(3);
   ...

This application code fragment will invoke the Java method onhand in class parts, because the procedure name specified on the CALL statement is found in the database and has the external name parts!onhand.

Example 2: There are six FOO procedures, in four different schemas, registered as follows (note that not all required keywords appear):

   CREATE PROCEDURE AUGUSTUS.FOO (INT) SPECIFIC FOO_1 ...
   CREATE PROCEDURE AUGUSTUS.FOO (DOUBLE, DECIMAL(15, 3)) SPECIFIC FOO_2 ...
   CREATE PROCEDURE JULIUS.FOO (INT) SPECIFIC FOO_3 ...
   CREATE PROCEDURE JULIUS.FOO (INT, INT, INT) SPECIFIC FOO_4 ...
   CREATE PROCEDURE CAESAR.FOO (INT, INT) SPECIFIC FOO_5 ...
   CREATE PROCEDURE NERO.FOO (INT,INT) SPECIFIC FOO_6 ...

The procedure reference is as follows (where I1 and I2 are INTEGER values):

   CALL FOO(I1, I2)

Assume that the application making this reference has an SQL path established as:

   "JULIUS", "AUGUSTUS", "CAESAR"

Following through the algorithm...

The procedure with specific name FOO_6 is eliminated as a candidate, because the schema "NERO" is not included in the SQL path. FOO_1, FOO_3, and FOO_4 are eliminated as candidates, because they have the wrong number of parameters. The remaining candidates are considered in order, as determined by the SQL path. Note that the types of the arguments and parameters are ignored. The parameters of FOO_5 exactly match the arguments in the CALL, but FOO_2 is chosen because "AUGUSTUS" appears before "CAESAR" in the SQL path.

Example 3: Assume the following procedure exists.

	 CREATE PROCEDURE update_order(
                       IN IN_POID BIGINT, 
                       IN IN_CUSTID BIGINT DEFAULT GLOBAL_CUST_ID,
                       IN NEW_STATUS VARCHAR(10) DEFAULT NULL,
                       IN NEW_ORDERDATE DATE DEFAULT NULL,
                       IN NEW_COMMENTS VARCHAR(1000)DEFAULT NULL)...

Also assume that the global variable GLOBAL_CUST_ID is set to the value 1002. Call the procedure to change the status of order 5000 for customer 1002 to 'Shipped'. Leave the rest of the order data as it is by allowing the rest of the arguments to default to the null value.

   CALL update_order (5000, NEW_STATUS => 'Shipped')

The customer with ID 1001 has called and indicated that they received their shipment for purchase order 5002 and are satisfied. Update their order.

   CALL update_order (5002,
		     IN_CUSTID => 1001,
		     NEW_STATUS => 'Received', 
		     NEW_COMMENTS => 'Customer satisfied with the order.')

Example 4: The following example illustrates procedure resolution, given two procedures named p1:

   CREATE PROCEDURE p1(i1 INT)...
   CREATE PROCEDURE p1(i1 INT DEFAULT 0, i2 INT DEFAULT 0)...
   CALL p1(i2=>1)

Starting with DB2 Version 9.7 Fix Pack 1, the argument names are taken into consideration during the candidate selection process. Therefore, only the second version of p1 will be considered a candidate. Furthermore, it can be successfully called because i1 in this version of p1 is defined with a default, so only specifying i2 on the call to p1 is valid.

Example 5: The following example is another illustration of procedure resolution, given two procedures named p1:

   CREATE PROCEDURE p1(i1 INT, i2 INT DEFAULT 0)...
   CREATE PROCEDURE p1(i1 INT DEFAULT 0, i2 INT DEFAULT 0, i3 INT DEFAULT 0)...
   CALL p1(i2=>1)

Starting with DB2 Version 9.7 Fix Pack 1, one of the criteria for a procedure parameter which does not have a corresponding argument in the CALL statement (specified by either position or name) is that the parameter is defined with a default value. Therefore, the first version of p1 is not considered a candidate.