CALL

The CALL statement invokes a stored procedure.

Invocation for CALL

This statement can be embedded in an application program. This statement can be executed interactively using the command line processor. Refer to Command line processor CALL statement for information about using the command line processor with the CALL statement. This statement can also be dynamically prepared, but only from an ODBC or CLI driver that supports dynamic CALL statements. IBM®'s ODBC and CLI drivers provide this capability.

Authorization for CALL

Invoking a stored procedure requires the EXECUTE privilege on the following:

  • The stored procedure

    You do not need the EXECUTE privilege on a stored procedure that was created prior to Version 6 of Db2 for z/OS®.

  • Additional authority is needed for the stored procedure package and most packages that run in the stored procedure.

    The authorization that is required for which packages is explained in detail in Authorization to execute packages under the stored procedure.

Authorization to execute the stored procedure

The authorization ID or role 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
    • DATAACCESS authority
    • SYSADM authority
  • For static SQL programs that use the syntax CALL variable, the authorization ID or role 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
    • DATAACCESS authority
    • SYSADM authority
    The DYNAMICRULES behavior for the plan or package that contains the CALL statement determines both the authorization ID or role and the privilege set that is held by that authorization ID or role:
    Run behavior
    The privilege set is the union of the set of privileges that are held by the SQL authorization ID and each authorization ID or role of the process.
    Bind behavior
    The privilege set is the privileges that are held by the primary authorization ID of the owner of the package or plan.
    Define behavior
    The privilege set is the privileges that are held by the authorization ID or role of the owner (definer) of the stored procedure or user-defined function that issued the CALL statement.
    Invoke behavior
    The privilege set is the privileges that are held by the authorization ID or role of the invoker of the stored procedure or user-defined function that issued the CALL statement. However, if the invoker is the primary authorization ID of the process or the CURRENT SQLID value, the privilege set is the union of the set of privileges that are held by each authorization ID or role.
    For a list of the DYNAMICRULES values that specify run, bind, define, or invoke behavior, see Table 1.

Authorization to execute packages under the stored procedure (including nested stored procedures)

The authorization that is required to run the stored procedure package and any packages that are used under the stored procedure (including nested stored procedures) apply to any form of the CALL statement as follows:

  • Stored procedure package: One of the authorization IDs or roles that are defined in Set of authorization IDs must have at least one of the following privileges or authorities on the stored procedure package:
    • The EXECUTE privilege
    • Ownership of the package
    • PACKADM authority for the package's collection
    • SYSADM authority
    A PKLIST entry is not required for the stored procedure package.
  • User-defined function packages and trigger packages: If a stored procedure or any application under the stored procedure invokes a user-defined function, Db2 requires only the owner (the definer), and not the invoker of the user-defined function, to have EXECUTE authority on the user-defined function package. However, the authorization ID or role of the SQL statement that invokes the user-defined function must have EXECUTE authority on the function.

    Similarly, if a trigger is used under a stored procedure, Db2 does not require EXECUTE authority on the trigger package; however, the authorization ID or role of the SQL statement that activates the trigger must have EXECUTE authority on the trigger.

    PKLIST entries are not required for any user-defined function packages or trigger packages that are used under the stored procedure.

  • Packages other than user-defined function, trigger, and stored procedure packages: One of the authorization IDs or roles that is defined below under Set of authorization IDs must have at least one of the following privileges or authorities on any packages other than user-defined function and trigger packages that are used under the stored procedure:
    • The EXECUTE privilege
    • Ownership of the package
    • PACKADM authority for the package's collection
    • SYSADM authority

    PKLIST entries are required for any of these packages that are used under the stored procedure.

For improved performance and simplicity, consider granting the EXECUTE ON PACKAGE privilege for the stored procedure package, and for any packages that run under the stored procedure, to the owner of the stored procedure.

Set of authorization IDs: Db2 checks the following authorization IDs, in the order in which they are listed, for the required authorization to execute the stored procedure package and any packages that run under the stored procedure other than user-defined function and trigger packages as described previously. Authorization checking ends after the first authorization ID that has EXECUTE ON PACKAGE privileges for the target package is found.

  • The owner (the definer) of the stored procedure.
  • The owner of the plan that contains the CALL statement that invokes the stored procedure if either of the following conditions is true:
    • The calling application (a package or a DBRM that is bound directly to the plan) is local.
    • The calling application is distributed, the Db2 subsystem is both the requester and the server, and the PRIVATE_PROTOCOL subsystem parameter is not set to NO.
  • The owner of the package that contains the CALL statement that invokes the stored procedure if the calling application is distributed and either of the following conditions is true:
    • The Db2 subsystem is the server but not the requester.
    • The Db2 subsystem is both the server and the requester and the PRIVATE_PROTOCOL subsystem parameter is set to NO.
    • The calling application uses Recoverable Resources Management Services attachment facility (RRSAF) and has no plan.
  • The authorization ID as determined by the value of the DYNAMICRULES bind option for the plan or package that contains the CALL statement if the CALL statement is in the form of CALL variable.
    • If the calling application is bound with the DYNAMICRULES(RUN) option, Db2 checks either the authorization ID of the process at run time and its secondary authorization IDs or the single authorization ID that is determined by the other DYNAMICRULES bind option values.
    • If the calling application is bound with a value other than DYNAMICRULES(RUN), Db2 checks only a single authorization ID, even if that ID fails the EXECUTE ON PACKAGE authorization check.
    • If the calling application is a package and is bound with DYNAMICRULES(BIND), Db2 checks the authorization ID of the package owner. Db2 does not check the authorization ID of the plan owner.

Start of changeAuthorization to execute packages for remote statements in a stored procedure: Remotely executed SQL statements in a stored procedure follow the same authorization rules as remotely executed SQL statements in a stand-alone application. If a stored procedure connects to a remote Db2 server and executes SQL statements at that server, the setting of the PRIVATE_PROTOCOL subsystem parameter at the server determines the IDs to which the EXECUTE privilege for the package that includes the SQL statements must be granted.End of change

Start of changeSee DRDA access with Db2 for z/OS only for more information.End of change

Syntax for CALL

Read syntax diagramSkip visual syntax diagramCALLprocedure-namevariable(,expressionNULLTABLEtransition-table-name)USING DESCRIPTORdescriptor-name

Description for CALL

procedure-name or variable
Identifies the procedure to call by the specified procedure-name or the procedure name contained in the variable. The identified procedure must exist at the current server.

If procedure-name specifies any of the three special characters that are alphabetic extenders for national languages, $#@, specify the procedure name with a variable.

If a variable is used:

  • It must be a CHAR or VARCHAR variable with a length attribute that is not greater than 254.
  • It must not be followed by an indicator variable.
  • The value of the variable is a specification that depends on the server. Regardless of the server, the specification must:
    • Be left justified within the variable
    • Not contain embedded blanks
    • Be padded on the right with blanks if its length is less than that of the 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 three-part procedure name.
    If the server is Db2 for z/OS, the specification must be a procedure name as defined above.

When the CALL statement is executed, the procedure name or specification must identify a stored procedure that exists at the 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.

expression, NULL, or TABLE transition-table-name
Identifies a list of values to be passed as arguments to the stored procedure. The nth value corresponds to the nth parameter in the procedure. Each parameter that is defined using CREATE PROCEDURE as OUT or INOUT must be specified as a variable.

The number of arguments that are specified must be the same as the number of parameters of a procedure that is defined at the current server with the specified procedure name.

If USING DESCRIPTOR is specified, each variable described by the identified SQLDA is an argument, or part of an expression that is an argument of the CALL statement. If host structures are not specified in the CALL statement, the nth argument of the CALL statement corresponds to the nth parameter in the stored procedure, and the number 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 arguments must be the same as the number of parameters defined for the stored procedure.

However, a character FOR BIT DATA argument cannot be passed as input for a parameter that is not defined as character FOR BIT DATA. Likewise, a character argument that is not FOR BIT DATA cannot be passed as input for a parameter that is defined as character FOR BIT DATA.

The attributes of the parameters are determined by the current 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

All parameters that are not variables are assumed to be input parameters (IN).

expression
The argument is the result of the specified expression, which is evaluated before the stored procedure is invoked.

If expression is a single 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. An expression can contain any of the following items:

  • Variable
  • Constant
  • Special register
  • Session global variable
  • Cast function with a variable or constant argument

A variable can identify a structure. Any 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 stored procedure must be defined as OUT.

expression must not reference an associative array value as an argument to a function if the procedure is remote.

The following additional rules apply depending on how the corresponding parameter was defined in the CREATE PROCEDURE statement for the procedure:

  • IN expression can contain references to multiple variables. In addition to the rules stated in Expressions for expression, expression cannot include a column name, a scalar subselect, a file reference variable, an aggregate function, or a user-defined function that is sourced on an aggregate function.
  • INOUT or OUT expression can only be a single variable. expression cannot include a file reference variable or an array element.
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. For information about creating a trigger, see CREATE TRIGGER (basic) and Creating a trigger.

Start of changeThere is no effect on the transition table on the return from the procedure regardless of whether the parameter was defined as IN, INOUT, or OUT, because transition tables are read-only.End of change

USING DESCRIPTOR descriptor-name
Identifies an SQLDA that contains a valid description of the 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.

There are additional considerations for setting the fields of the SQLDA when a variable that is passed as a parameter to the stored procedure has a LOB data type or is a LOB locator. For more information, see SQL descriptor area (SQLDA).

The SQL CALL statement ignores distinct type information in the SQLDA. Only the base SQL type information is used to process the input and output parameters described by the SQLDA.

In REXX, only variables USING DESCRIPTOR is supported. Since global variables are not supported within the SQLDA, global variable are not supported in REXX.

See Identifying an SQLDA in C or C++ for how to represent descriptor-name in C.

Notes for CALL

Parameter assignments: When the CALL statement is executed, the value of each of its arguments is assigned with storage assignment rules to the corresponding IN or INOUT parameter of the stored procedure. In cases where the arguments of the CALL statement are not an exact match to the data types of the parameters of the stored procedure, each argument 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.

Control is passed to the stored procedure according to the calling conventions of the host language.

When execution of the stored procedure is complete, the value of each parameter of the stored procedure defined as OUT or INOUT is assigned to the corresponding argument of the CALL statement. If an error is returned by the procedure, OUT arguments are undefined, and INOUT arguments are unchanged.

A timestamp without time zone value must not be assigned to a timestamp with time zone target.

The following rules apply when the value of an array argument is assigned to the corresponding array parameter:

  • For a local procedure call: The argument and the parameter must be defined as the same array type.
  • For a remote procedure call: The data type of the elements of the array argument must be the same as the data type of the elements of the array parameter. In addition, for IN or OUT parameters, all of the relevant conditions in one of the rows in the following table must be true. For INOUT parameters, all of the relevant conditions in row 1 of the following table must be true, or all of the relevant conditions in rows 2 and 3 must be true. A relevant condition is indicated with Y.
    Relationship of argument to associated parameter Relationship applies to IN parameter Relationship applies to OUT parameter Relationship applies to INOUT parameter
    The argument is an ordinary array, the parameter is an ordinary array, and the argument and parameter are defined with the same data type for the array indexes. Y Y Y
    The argument is an ordinary array, the parameter is an associative array type, the parameter is an IN or INOUT parameter, and the data type of the array indexes is INTEGER. The associative array parameter is assigned an associative array value that is derived from the ordinary array argument value. The values of the array elements in the ordinary array are assigned to the target associative array parameter, in the same order as their order in the ordinary array argument. The index values in the target associative array parameter are assigned from 1 to the cardinality of the ordinary array argument value. Y   Y
    The argument is an ordinary array type, the parameter is an associative array type, and the parameter is an INOUT or OUT parameter. The argument is assigned an ordinary array value that is derived from the associative array parameter value. The values of the array elements in the associative array value are assigned to the target ordinary array, in an order determined by Db2. The index values in the target ordinary array argument are assigned from 1 to the cardinality of the associative array parameter value. The index values from the associative array parameter value are ignored.   Y Y

For details on the rules used to assign parameters, see Assignment and comparison.

Conversion can occur when precision, scale, length, encoding scheme, or CCSID differ between the argument specified in the CALL statement and the data type of the corresponding parameter of the stored procedure. Conversion might occur for a character string argument 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 an argument of mixed data that actually contains DBCS characters as input for a parameter of the stored procedure that is defined as FOR SBCS DATA. Likewise, an error occurs when the stored procedure returns mixed data that actually contains DBCS characters for an argument of the CALL statement that is defined as FOR SBCS DATA.

Procedure signatures: A procedure is identified by its schema, a procedure name, and its number of parameters. This is called a procedure signature, which must be unique within the database. Db2 for z/OS does not support overloaded procedure names (procedures with the same schema and procedure name, but with different numbers of parameters).

SQL path: A procedure can be invoked by referring to a qualified name (schema and procedure name), followed by an optional list of arguments that are enclosed in parentheses. A procedure can also be invoked without the schema name, which results in a choice of possible procedures in different schemas that have the same procedure name and 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 as the procedure in the CALL statement. For CALL statements that explicitly specify a procedure name, the SQL path is specified by using the platform-specific bind option. For CALL variable statements, the SQL path is the value of the CURRENT PATH special register when the procedure is invoked.

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

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 an SQL identifier that contains the location name that identifies the DBMS at which the procedure is stored. The second part is an SQL identifier that contains the schema name of the stored procedure. The last part is an SQL 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 server. If the server is Db2 for z/OS, 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 string constant 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 variable is specified for the procedure name on the CALL statement (CALL 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.
    If the list of schemas in the SQL path is exhausted before the procedure name is resolved, an error is returned.

When the procedure is resolved depends on how the procedure name is specified. For a CALL statement that specifies the procedure name using a variable, procedure resolution occurs at run time. For a CALL statement that contains the name of the procedure as an identifier, procedure resolution occurs when the CALL statement is bound.

Procedure resolution is done by the database manager using the following steps:

  1. Find all procedures from the catalog where all of the following conditions are true:
    • For invocations where the schema name is specified (qualified references), the schema name and the procedure name match the invocation name.

      For invocations where the schema name is not specified (unqualified references), the procedure name matches the invocation name, and the procedure has a schema name that matches one of the schemas in the SQL path.

    • The number of defined parameters matches the number of arguments that are specified in the invocation.
    • The invoker has the EXECUTE privilege on the procedure.
  2. Of the candidate procedures that remain from step 1, choose the procedure whose schema is first in the SQL path. If no candidate procedures remain after step 1, an error is returned.
  3. For CALL statements that use a variable to specify the procedure name, the CURRENT ROUTINE VERSION special register can affect which version of the native SQL procedure is invoked. If the CURRENT ROUTINE VERSION special register is set, check if there is a version of the procedure with that version name. If not, choose the currently active version of the procedure.

    For CALL statements that do not use a variable to specify the procedure name, choose the currently active version of the procedure.

Version resolution: Normally, the currently active version of a native SQL procedure will be used on a CALL statement. However, if the CALL statement is a recursive call inside the body of the same stored procedure, and the original CALL statement uses a version that is different from the currently active version, the active version will not be used. The version from the original CALL statement will be used for any recursive CALL statements until the entire stored procedure finishes executing. This preserves the semantics of the version that is used by the original CALL statement. This includes the case where the recursive call is indirect. For example, assume that procedure SP1 call procedure SP2, which in turn recursively calls SP1. The second invocation of procedure SP1 will use the version of the procedure that is active at the time of the original CALL statement that invoked procedure SP1.

Since the currently active version can be used at the next CALL statement, it is possible that two or more versions of the same procedure can run at the same time. There could be different versions of an SQL procedure loaded by a given thread. For example, a CALL SP1 statement in an application will cause the currently active version, SP1_V1, to load and execute. After this CALL statement has completed, an ALTER PROCEDURE ALTER ACTIVE VERSION could execute and change the active version of the procedure SP1 to version SP1_V2. Subsequent CALL SP1 statements from the same thread will load the currently active version of the procedure, SP1_V2, and execute it.

Parameter assignments: When the CALL statement is executed, the value of each of its parameters is assigned with 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 with storage assignment rules to the corresponding parameter 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 Assignment and comparison.

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.

Result sets from procedures: Any cursors specified using the WITH RETURN clause that the procedure leaves open when it returns identifies a result set. In a procedure written in Java™, all cursors are implicitly defined WITH RETURN.

Results sets are returned only when the procedure is called from CLI, JDBC, or SQLJ. If the procedure was invoked from CLI or Java, and more than one cursor is left open, the result sets can only be processed in the order in which the cursors were opened. Only unread rows are available to be fetched. 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, then rows 151 through 500 will be returned to the procedure.

Errors from procedures: A procedure can return errors or warnings using an SQLSTATE like other SQL statements. Applications should be aware of the possible SQLSTATEs that can be expected when a procedure is invoked. The possible SQLSTATEs depend on how the procedure is coded. Procedures might also return SQLSTATEs such as those that begin with '38' or '39' if Db2 encounters problems executing the procedure. Applications should therefore be prepared to handle any error SQLSTATE that can result from issuing a CALL statement.

Improving performance: The capability of calling stored procedures is provided to improve the performance of DRDA distributed access. The capability is also useful for local operations. The server can be the local Db2. In which case, packages are still required.

All values of all parameters are passed from the requester to the server. To improve the performance of this operation, variables that correspond to OUT parameters and have lengths of more than a few bytes should be set to null before the CALL statement is executed.

Using the CALL statement in a trigger: When a trigger issues a CALL statement to invoke a stored procedure, the parameters that are specified in the CALL statement cannot be variables and the USING DESCRIPTOR clause cannot be specified.

Nesting CALL statements: 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 64 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. For example, Figure 1 illustrates a scenario in which a client program calls stored procedure PROCA, which in turn calls stored procedure PROCB. Only PROCA can access any result sets that PROCB returns; the client program has no access to the query result sets. The number of query result sets that PROCB returns does not count toward the maximum number of query results that PROCA can return.
Figure 1. Nested CALL statements
Begin figure description. A client program issues EXEC SQL CALL PROCA. PROCA issues the nested CALL statement EXEC SQL CALL PROCB. PROCB issues the statement EXEC SQL OPEN C1. End figure description.

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. Procedures that are defined with the AUTONOMOUS attribute cannot call other procedures that are defined with the AUTONOMOUS attribute.

Multiple calls to the same stored procedure: You can call a stored procedure multiple times within an application and at the same nesting level. Each call to the same stored procedure causes a unique instance of the stored procedure to run. If the stored procedure returns result sets, each instance of the stored procedure opens its own set of result set cursors.

The application might receive a "resource unavailable message" if the CALL statement causes the values of the maximum number of active stored procedures or maximum number open cursors to be exceeded. The value of field MAX STORED PROCEDURES (on installation panel DSNTIPX) defines the maximum number of active stored procedures that are allowed per thread. The value of field MAX OPEN CURSORS (on installation panel DSNTIPX) defines the maximum number of open cursors (both result set cursors and regular cursors) that are allowed per thread.

If you make multiple calls to the same stored procedure within an application, be aware of the following considerations:

  • A DESCRIBE PROCEDURE statement describes the last instance of the stored procedure.
  • The ASSOCIATE LOCATORS statement works on the last instance of the stored procedure.
  • The ALLOCATE CURSOR statement must specify a unique cursor name for a result set returned from an instance of the stored procedure. Otherwise, you will lose the data from the result sets that are returned from prior instances or calls to the stored procedure.

You should issue an ASSOCIATE LOCATORS statement (or DESCRIBE PROCEDURE statement) after each call to the stored procedure to get a unique locator value for each result set.

Using variables: If the CALL statement contains variables, the contents of the variables are assumed to be in the encoding scheme that was specified in the ENCODING parameter when the package or plan that contains the statement was bound.

Examples for CALL

Example 1: A PL/I application has been precompiled on Db2 ALPHA and a package was created at Db2 BETA with the BIND subcommand. A CREATE PROCEDURE statement was issued at BETA to define the procedure SUMARIZE, which allows nulls and has two parameters. The first parameter is defined as IN and the second parameter is defined as OUT. Some of the statements that the application that runs at Db2 ALPHA might use to call stored procedure SUMARIZE include:
EXEC SQL CONNECT TO BETA;
V1 = 528671;
IV = -1;
EXEC SQL CALL SUMARIZE(:V1,:V2 INDICATOR :IV);
Example 2: Suppose that stored procedure MYPROC exists and produces several result sets. An application might include statements like the following to access the result sets produced by MYPROC:
-- Invoke stored procedure MYPROC that returns several result sets
EXEC SQL CALL MYPROC (....);
-- Copy the locator values for the result sets into result set locator variables
EXEC SQL ASSOCIATE RESULT SET LOCATORS (:RS1, :RS2, :RS3) WITH PROCEDURE MYPROC;
-- Allocate cursors for the result set cursors
EXEC SQL ALLOCATE CSR1 CURSOR FOR RESULT SET :RS1;
EXEC SQL ALLOCATE CSR2 CURSOR FOR RESULT SET :RS2;
EXEC SQL ALLOCATE CSR3 CURSOR FOR RESULT SET :RS3;
-- Process data returned with the result set cursors
DO WHILE (SQLCODE = 0); 
EXEC SQL FETCH CSR1 INTO .....
END; 
EXEC SQL CLOSE CSR1;
-- do similar processing with other result sets
...

Example 3: Suppose that procedure FIND_CUSTOMERS has the following parameters:

  • An IN parameter that is an array of phone numbers
  • An IN parameter that is a prefix value to search for a match
  • An OUT parameter that returns an array of phone numbers

FIND_CUSTOMERS searches the input array variable for phone numbers that match the prefix value, and returns an array that contains the phone numbers that match the prefix value.

Start of changeThe input and output array variables are defined as follows:End of change

Start of change

CREATE TYPE PHONENUMBERS AS VARCHAR(20) CCSID UNICODE ARRAY[10]; 
                                            -- Create an array type
CREATE VARIABLE PNUMBER_ARRAY PHONENUMBERS; -- Create input array variable
CREATE VARIABLE PNUMBER_ARRAY_OUT PHONENUMBERS;
                                            -- Create output array variable
End of change

FIND_CUSTOMERS looks like this:


---------------------------------------------------------------------------
-- Create an SQL procedure with array parameters. The array parameters are 
-- defined with the PHONENUMBERS array type.  The procedure searches for 
-- numbers in IN_PHONENUMBERS that begin with the given prefix, and returns
-- the phone numbers in the NUMBERS_OUT parameter. 
---------------------------------------------------------------------------
CREATE PROCEDURE FIND_CUSTOMERS(
  IN NUMBERS_IN PHONENUMBERS,
  IN PREFIX CHAR(3),
  OUT NUMBERS_OUT PHONENUMBERS)
BEGIN
  DECLARE I, J INTEGER;

  SET I = 1;
  SET J = 1;

-- Initialize NUMBERS_OUT to an empty array using an array constructor 
-- with no elements
  SET NUMBERS_OUT = ARRAY[ ];
  WHILE i < CARDINALITY(NUMBERS_IN) DO
    IF SUBSTR(NUMBERS_IN[I], 1, 3) = PREFIX THEN
      SET NUMBERS_OUT[J] = NUMBERS_IN[I];
      SET J = J + 1;
    END IF;
    SET I = I + 1;
  END WHILE;
END %  

Start of changeIn the client program, initialize the input array with values from an array constructor, and then invoke the procedure:End of change

Start of change
SET  PNUMBER_ARRAY = ARRAY['416-305-3745',    
                           '905-414-4565',     
                           '416-305-3746'];     
CALL FIND_CUSTOMERS(PNUMBER_ARRAY,      -- NUMBERS_IN parameter (IN parm)
                    ‘416’,              -- PREFIX parameter (IN parm)
                    PNUMBER_ARRAY_OUT); -- NUMBERS_OUT parameter (OUT parm) 
End of change

The CALL statement returns an array value with the following information in the argument corresponding to the NUMBERS_OUT parameter, which sets the PNUMBER_ARRAY_OUT variable:

[‘416-305-3745’,
 ‘416-305-3746’]