CALL

The CALL statement calls a procedure.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

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

Start of change If a global variable is referenced as an IN or INOUT parameter, the privileges held by the authorization ID for the statement must include:
  • The READ privilege on the global variable.
End of change
Start of change If a global variable is referenced as an OUT or INOUT parameter, the privileges held by the authorization ID for the statement must include:
  • The WRITE privilege on the global variable.
End of change

For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-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-'                          

Notes:
  1. If an SQL descriptor is specified in the USING clause and the INTO clause is not specified, USING DESCRIPTOR is not allowed and USING SQL DESCRIPTOR must be specified.

Description

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 a variable is specified:

  • Start of changeIt must be a character-string variable or Unicode graphic-string. It cannot be a global variable.End of change
  • It must not be followed by an indicator variable.
  • The procedure name that is contained within the variable must be left-justified and must be padded on the right with blanks if its length is less than that of the variable.
  • The name of the procedure must be in uppercase unless the procedure name is a delimited name.

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:

  • The DECLARE PROCEDURE statement determines the name of the external program, language, and calling convention.
  • The attributes of the parameters of the procedure are defined by the DECLARE PROCEDURE statement.

Otherwise:

  • The current server determines the name of the external program, language, and calling convention.
  • If the current server is DB2 for i:
    • The external program name is assumed to be the same as the external procedure name.
    • If the program attribute information associated with the program identifies a recognizable language, then that language is used. Otherwise, the language is assumed to be C.
    • The calling convention is assumed to be GENERAL.
  • The application requester assumes all parameters that are variables or parameter markers are INOUT. All parameters that are not variables are assumed to be IN.
  • The actual attributes of the parameters are determined by the current server.

    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

Start of changeargument-listEnd of change
Identifies a list of values to be passed as parameters to the procedure. The nth value corresponds to the nth parameter in the procedure.

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.

Start of changeexpressionEnd of change
Start of changeAn 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.End of change
NULL
Specifies a null value as an argument to the procedure.
SQL-descriptors
If SQL descriptors are specified on CALL, a procedure that has IN and INOUT parameters requires an SQL descriptor to be specified in the USING clause; and a procedure that has OUT or INOUT parameters requires an SQL descriptor to be specified in the INTO clause. If all the parameters of the procedure are INOUT parameters, the same descriptor can be used for both clauses. For more information, see Multiple SQL descriptors on CALL.
INTO
Identifies an SQL descriptor which contains valid descriptions of the output variables to be used with the CALL statement. Before the CALL statement is executed, a descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. The COUNT field in the descriptor header must be set to reflect the number of OUT and INOUT parameters for the procedure. The item information, including TYPE, and where applicable, DATETIME_INTERVAL_CODE, LENGTH, DB2_CCSID, PRECISION, and SCALE, must be set for the variables that are used when processing the statement.
LOCAL
Specifies the scope of the name of the descriptor to be local to program invocation. The information is returned from the descriptor known in this local scope.
GLOBAL
Specifies the scope of the name of the descriptor to be global to the SQL session. The information is returned from the descriptor known to any program that executes using the same database connection.
SQL-descriptor-name
Names the SQL descriptor. The name must identify a descriptor that already exists with the specified scope.

Start of changeSee GET DESCRIPTOR for an explanation of the information that is placed in the SQL descriptor.End of change

USING
Identifies an SQL descriptor which contains valid descriptions of the input variables to be used with the CALL statement. Before the CALL statement is executed, a descriptor must be allocated using the ALLOCATE DESCRIPTOR statement. The COUNT field in the descriptor header must be set to reflect the number of IN and INOUT parameters for the procedure. The item information, including TYPE, and where applicable, DATETIME_INTERVAL_CODE, LENGTH, DB2_CCSID, PRECISION, and SCALE, must be set for the variables that are used when processing the statement. The DATA item and when nulls are used, the INDICATOR item, must be set for the input variables.
LOCAL
Specifies the scope of the name of the descriptor to be local to program invocation.
GLOBAL
Specifies the scope of the name of the descriptor to be global to the SQL session.
SQL-descriptor-name
Names the SQL descriptor. The name must identify a descriptor that already exists with the specified scope.

Start of changeSee SET DESCRIPTOR for an explanation of the information in the SQL descriptor.End of change

USING DESCRIPTOR descriptor-name
Identifies an SQLDA that must contain a valid description of variables.
Before the CALL statement is processed, you must set the following fields in the SQLDA. (The rules for REXX are different. For more information, see the Embedded SQL Programming topic collection.)
  • SQLN to indicate the number of SQLVAR occurrences provided in the SQLDA
  • SQLDABC to indicate the number of bytes of storage allocated for the SQLDA
  • SQLD to indicate the number of variables used in the SQLDA when processing the statement
  • SQLVAR occurrences to indicate the attributes of the variables.
The SQLDA must have enough storage to contain all SQLVAR occurrences. Therefore, the value in SQLDABC must be greater than or equal to 16 + SQLN*(80), where 80 is the length of an SQLVAR occurrence. If LOBs or distinct types are specified, there must be two SQLVAR entries for each parameter marker and SQLN must be set to two times the number of parameter markers.

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.

Notes

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 Start of changestorage assignment rules for SQL parameters, otherwise usingEnd of change 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.

Start of changeGlobal variables:End of change 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.

Start of changeResult sets from procedures: There are three ways to return result sets from a procedure: End of change

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.

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.

Examples

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'.

Start of change

Example 4: Call procedure PGM2 on relational database BRANCHRDB2 and pass one parameter.

      CALL BRANCHRDB2.SCHEMA3.PGM2 (:hv1)
End of change
1 Note that in the case of decimal constants, leading zeroes are significant when determining the attributes of the argument. Normally, leading zeroes are not significant.