CALL

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:

• If the procedure is an SQL procedure:
  • The EXECUTE privilege on the procedure, and
  • The system authority *EXECUTE on the library containing the SQL procedure
• If the procedure is a Java™ external procedure:
  • Read authority (*R) to the integrated file system file that contains the Java class.
  • Read and execute authority (*RX) to all directories that must be accessed in order to find the integrated file system file.
• If the procedure is a REXX external procedure:
  • The system authorities *OBJOPR, *READ, and *EXECUTE on the source file associated with the procedure,
  • The system authority *EXECUTE on the library containing the source file, and
  • The system authority *USE to the CL command,
• If the procedure is an external procedure, but not a REXX or Java external procedure:
  • The system authority *EXECUTE on the program or service program associated with the procedure, and
  • The system authority *EXECUTE on the library containing the program or service program associated with the procedure
• Administrative authority

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.

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.

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

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:

• It must be a character-string variable or Unicode graphic-string. It cannot be a global variable.
• 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 DB2® for i, 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.

If there is no DECLARE PROCEDURE and the procedure name identifies a procedure that was defined by a CREATE PROCEDURE statement, and the current server is DB2 for i, then:

• The CREATE 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 CREATE 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. ¹

argument-list

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 must be specified as a variable. A default cannot be specified for an OUT parameter. If a default is used for an INOUT parameter, then the default expression is used to initialize the parameter for input to the procedure. No value is returned for this parameter when the procedure exits.

When a procedure is called, arguments must be specified for all parameters that are not defined to have a default value. When an argument is assigned to a parameter using the named syntax, then all the arguments that follow it must also be assigned using the named syntax.

Any references to date, time, or timestamp special register values in the argument list will use one clock reading for any default expressions and a separate clock reading for any references in the explicit arguments.

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.

parameter-name

Name of the parameter to which the argument value is assigned. The name must match a parameter name defined for the procedure. Named arguments correspond to the same named parameter regardless of the order in which they are specified in the argument list. When an argument is assigned to a parameter by name, all the arguments that follow it must also be assigned by name.

A named argument must be specified only one time (implicitly or explicitly).

Named arguments are not allowed on a call to a procedure that was not defined using a CREATE PROCEDURE statement.

expression

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

DEFAULT

Specifies the default as defined in the CREATE PROCEDURE statement is used as an argument to the procedure. If no default is defined for the parameter, the NULL value is used.

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.

See GET DESCRIPTOR for an explanation of the information that is placed in the SQL descriptor.

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.

See SET DESCRIPTOR for an explanation of the information in the SQL descriptor.

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. A parameter value that is defined to have a default value can be omitted from the argument list when invoking 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 storage assignment rules for SQL parameters, otherwise using 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.

Global variables: A global variable may be specified and will be modified if used as a parameter that is INOUT or OUT.

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.

Procedure resolution: For details of how procedure resolution is performed, see Procedure resolution .

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.

Result sets from procedures: There are three ways to return result sets from a procedure:

• If a SET RESULT SETS statement is executed in the procedure, the last SET RESULT SETS statement executed in the procedure identifies the result sets. The result sets are returned in the order specified on the SET RESULT SETS statement.
• If a SET RESULT SETS statement is not executed in the procedure,
  • If no cursors have specified a WITH RETURN clause, each cursor that the procedure opens and leaves open when it returns identifies a result set. The result sets are returned in the order in which the cursors are opened.
  • If any cursors have specified a WITH RETURN clause, each cursor that is defined with the WITH RETURN clause that the procedure opens and leaves open when it returns identifies a result set. The result sets are returned in the order in which the cursors are opened.

When a result set is returned using an open cursor, the rows are returned starting with the current cursor position.

If a cursor result set references an SQL array type, an error might be returned when referencing the result set.

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:

If a second procedure is invoked within the execution of the current procedure, an error is issued if:

• The invoked procedure possibly contains SQL and the invoking procedure does not allow SQL
• The invoked procedure reads SQL data and the invoking procedure does not allow reading SQL data
• The invoked procedure modifies SQL data and the invoking procedure does not allow modifying SQL data

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 the input SQL descriptor attributes were set using DESCRIBE INPUT and the output SQL descriptor attributes were set using DESCRIBE (OUTPUT), the SQL descriptors will have attributes that match the actual procedure definition at the current server prior to calling the procedure. In this case, the output SQL descriptor will contain one variable for each OUT and INOUT parameter. Likewise, the input SQL descriptor will contain one variable for each IN and INOUT parameter.

  This is the recommended technique for specifying multiple SQL descriptors on a CALL statement.

• Otherwise, the actual procedure definition at the current server is unknown prior to calling the procedure, so each parameter is assumed to be INOUT at the time the procedure is called. This means that both SQL descriptors must be specified, and since each parameter is assumed to be INOUT, they must have the same number of variables and the TYPE, DATETIME_INTERVAL_CODE, LENGTH, DB2_CCSID, PRECISION, and SCALE of each variable in the output SQL descriptor must be exactly the same as the corresponding variable in the input SQL descriptor. Otherwise, an error is returned.

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.

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