DECLARE PROCEDURE

The DECLARE PROCEDURE statement defines an external procedure.

Invocation

This statement can only be embedded in an application program. It is not an executable statement. It must not be specified in REXX.

Authorization

None.

Syntax

parameter-declaration

data-type

XML-cast-type

option-list

Notes:

¹ The optional clauses can be specified in a different order.

built-in-type

ccsid-clause

normalize-clause

Description

procedure-name

Names the procedure. The name must not be the same as the name of another procedure declared in your source program.

(parameter-declaration,...)

Specifies the number of parameters of the procedure and the data type of each parameter. A parameter for a procedure can be used only for input, only for output, or for both input and output. Although not required, you can give each parameter a name.

The maximum number of parameters allowed in CREATE PROCEDURE depends on the type of language and the parameter style:

For JAVA and ILE programs and service programs, the maximum is 2000.
For OPM programs and REXX,
- If PARAMETER STYLE GENERAL is specified the maximum is 255.
- If PARAMETER STYLE GENERAL WITH NULLS is specified the maximum is 254.
- If PARAMETER STYLE SQL is specified the maximum is 254.

The maximum number of parameters can be further limited by the maximum number of parameters allowed by the language.

IN

Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned.¹

OUT

Identifies the parameter as an output parameter that is returned by the procedure.

A DataLink or a distinct type based on a DataLink may not be specified as an output parameter.

INOUT

Identifies the parameter as both an input and output parameter for the procedure.

A DataLink or a distinct type based on a DataLink may not be specified as an input and output parameter.

parameter-name

Names the parameter. The name cannot be the same as any other parameter-name for the procedure.

data-type

Specifies the data type of the parameter.

The data type must be valid for the language specified in the language clause. All data types are valid for SQL procedures. DataLinks are not valid for external procedures. For more information about data types, see CREATE TABLE, and the SQL Programming topic collection.

If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the procedure. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the procedure is called.

Any parameter that has an XML type must specify the XML-cast-type clause.

AS LOCATOR

Specifies that the parameter is a locator to the value rather than the actual value. You can specify AS LOCATOR only if the parameter has a LOB or XML data type or a distinct type based on a LOB or XML data type. If AS LOCATOR is specified, FOR SBCS DATA or FOR MIXED DATA must not be specified.

AS XML-cast-type

Specifies the data type passed to the procedure for a parameter that is XML type or a distinct type based on XML type. If LOCATOR is specified, the parameter is a locator to the value rather than the actual value.

If a CCSID value is specified, only Unicode CCSID values can be specified for graphic data types. If a CCSID value is not specified, the CCSID is established at the time the containing program, module, or service program is created according to the SQL_XML_DATA_CCSID QAQQINI option setting. The default CCSID is 1208. See XML Values for a description of this option.

DYNAMIC RESULT SETS integer

Specifies the maximum number of result sets that can be returned from the procedure. integer must be greater than or equal to zero and less than 32768. If zero is specified, no result sets are returned. If the SET RESULT SETS statement is issued, the number of results returned is the minimum of the number of result sets specified on this keyword and the SET RESULT SETS statement.

For more information about result sets, see SET RESULT SETS.

LANGUAGE

Specifies the language that the external program is written in. The language clause is required if the external program is a REXX procedure.

If LANGUAGE is not specified, the LANGUAGE is determined from the program attribute information associated with the external program. If the program attribute information associated with the program does not identify a recognizable language, then the language is assumed to be C.

C: The external program is written in C.
C++: The external program is written in C++.
CL: The external program is written in CL.
COBOL: The external program is written in COBOL.
COBOLLE: The external program is written in ILE COBOL.
JAVA: The external program is written in JAVA.
PLI: The external program is written in PL/I.
REXX: The external program is a REXX procedure.
RPG: The external program is written in RPG.
RPGLE: The external program is written in ILE RPG.

PARAMETER STYLE

Specifies the conventions used for passing parameters to and returning the values from procedures:

SQL

Specifies that in addition to the parameters on the CALL statement, several additional parameters are passed to the procedure. The parameters are defined to be in the following order:

The first N parameters are the parameters that are specified on the DECLARE PROCEDURE statement.
N parameters for indicator variables for the parameters.
A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates the success or failure of the procedure. The SQLSTATE returned is assigned by the external program.
The user may set the SQLSTATE to any valid value in the external program to return an error or warning from the function.
A VARCHAR(517) input parameter for the fully qualified procedure name.
A VARCHAR(128) input parameter for the specific name.
A VARCHAR(1000) output parameter for the message text.

The following additional parameter may be passed as the last parameter:

A parameter for the dbinfo structure, if DBINFO was specified on the CREATE PROCEDURE statement.

These parameters are passed according to the specified LANGUAGE. For example, if the language is C or C++, the VARCHAR parameters are passed as NUL-terminated strings. For more information about the parameters passed, see the include sqludf in the appropriate source file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.

PARAMETER STYLE SQL cannot be used with LANGUAGE JAVA.

DB2GENERAL

Specifies that the procedure will use a parameter passing convention that is defined for use with Java™ methods.

PARAMETER STYLE DB2GENERAL can only be specified with LANGUAGE JAVA. For details on passing parameters in JAVA, see the IBM® Developer Kit for Java topic collection.

GENERAL

Specifies that the procedure will use a parameter passing mechanism where the procedure receives the parameters specified on the CALL. Additional arguments are not passed for indicator variables.

PARAMETER STYLE GENERAL cannot be used with LANGUAGE JAVA.

GENERAL WITH NULLS

Specifies that in addition to the parameters on the CALL statement as specified in GENERAL, another argument is passed to the procedure. This additional argument contains an indicator array with an element for each of the parameters of the CALL statement. In C, this would be an array of short INTs. For more information about how the indicators are handled, see the SQL Programming topic collection.

PARAMETER STYLE GENERAL WITH NULLS cannot be used with LANGUAGE JAVA.

JAVA

Specifies that the procedure will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. INOUT and OUT parameters will be passed as single entry arrays to facilitate returning values. For increased portability, you should write Java procedures that use the PARAMETER STYLE JAVA conventions.

PARAMETER STYLE JAVA can only be specified with LANGUAGE JAVA. For details on passing parameters in JAVA, see the IBM Developer Kit for Java topic collection.

Note that the language of the external function determines how the parameters are passed. For example, in C, any VARCHAR or CHAR parameters are passed as NUL-terminated strings. For more information, see SQL Programming. For Java routines, see IBM Developer Kit for Java.

SPECIFIC specific-name

Specifies a qualified or unqualified name that uniquely identifies the procedure. The specific-name, including the implicit or explicit qualifier, must be the same as the procedure-name.

If no qualifier is specified, the implicit or explicit qualifier of the procedure-name is used. If a qualifier is specified, the qualifier must be the same as the explicit or implicit qualifier of the procedure-name.

If specific-name is not specified, it is the same as the procedure name.

DETERMINISTIC or NOT DETERMINISTIC

Specifies whether the procedure returns the same results each time the procedure is called with the same IN and INOUT arguments.

NOT DETERMINISTIC: The procedure may not return the same result each time the procedure is called with the same IN and INOUT arguments, even when the referenced data in the database has not changed.
DETERMINISTIC: The procedure always returns the same results each time the procedure is called with the same IN and INOUT arguments, provided the referenced data in the database has not changed.

MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, or NO SQL

Specifies which SQL statements, if any, may be executed in the procedure or any routine called from this procedure. The default is MODIFIES SQL DATA. See Characteristics of SQL statements for a detailed list of the SQL statements that can be executed under each data access indication.

MODIFIES SQL DATA: Specifies that the procedure can execute any SQL statement except statements that are not supported in procedures.
READS SQL DATA: Specifies that SQL statements that do not modify SQL data can be included in the procedure.
CONTAINS SQL: Specifies that SQL statements that neither read nor modify SQL data can be executed by the procedure.
NO SQL: Specifies that the procedure cannot execute any SQL statements.

CALLED ON NULL INPUT

Specifies that the function is to be invoked, if any, or all, argument values are null, making the function responsible for testing for null argument values. The function can return a null or nonnull value.

FENCED or NOT FENCED

This parameter is allowed for compatibility with other products and is not used by Db2® for i.

PROGRAM TYPE MAIN or PROGRAM TYPE SUB

This parameter is allowed for compatibility with other products. It indicates whether the routine's external program is a program (*PGM) or a procedure in a service program (*SRVPGM).

PROGRAM TYPE MAIN: Specifies that the routine executes as the main entry point in a program. The external program must be a *PGM object.
PROGRAM TYPE SUB: Specifies that the procedure executes as a procedure in a service program. The external program must be a *SRVPGM object.

DBINFO

Specifies that the database manager should pass a structure containing status information to the procedure. Table 1 contains a description of the DBINFO structure. Detailed information about the DBINFO structure can be found in include sqludf in the appropriate source file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.

DBINFO is only allowed with PARAMETER STYLE DB2SQL.

Table 1. DBINFO fields
Field	Data Type	Description
Relational database	VARCHAR(128)	The name of the current server.
Authorization ID	VARCHAR(128)	The run-time authorization ID.
CCSID Information	INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER INTEGER CHAR(8)	The CCSID information of the job. Three sets of three CCSIDs are returned. The following information identifies the three CCSIDs in each set: SBCS CCSID DBCS CCSID Mixed CCSID Following the three sets of CCSIDs is an integer that indicates which set of three sets of CCSIDs is applicable and eight bytes of reserved space. Each set of CCSIDs is for a different encoding scheme (EBCDIC, ASCII, and Unicode). If a CCSID is not explicitly specified for a parameter on the CREATE PROCEDURE statement, the input string is assumed to be encoded in the CCSID of the job at the time the procedure is executed. If the CCSID of the input string is not the same as the CCSID of the parameter, the input string passed to the external procedure will be converted before calling the external program.
Target Column	VARCHAR(128) VARCHAR(128) VARCHAR(128)	Not applicable for a call to a procedure.
Version and release	CHAR(8)	The version, release, and modification level of the database manager.
Platform	INTEGER	The server's platform type.

EXTERNAL NAME external-program-name

Specifies the program that will be executed when the procedure is called by the CALL statement. The program name must identify a program that exists at the application server. The program cannot be an ILE service program.

The validity of the name is checked at the application server. If the format of the name is not correct, an error is returned.

If external-program-name is not specified, the external program name is assumed to be the same as the procedure name.

Notes

DECLARE PROCEDURE scope: The scope of the procedure-name is the source program in which it is defined; that is, the program submitted to the precompiler. Thus, a program called from another separately compiled program or module will not use the attributes from a DECLARE PROCEDURE statement in the calling program.

DECLARE PROCEDURE rules: The DECLARE PROCEDURE statement should precede all CALL statements that reference that procedure.

The DECLARE PROCEDURE statement only applies to static CALL statements. It does not apply to any dynamically prepared CALL statements or a CALL statement where the procedure name is identified by a variable.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

The keywords VARIANT and NOT VARIANT can be used as synonyms for NOT DETERMINISTIC and DETERMINISTIC.
The keywords NULL CALL can be used as synonyms for CALLED ON NULL INPUT.
The keywords SIMPLE CALL can be used as a synonym for GENERAL.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.
The keywords PARAMETER STYLE DB2SQL can be used as a synonym for PARAMETER STYLE SQL.

Example

Declare an external procedure PROC1 in a C program. When the procedure is called using the CALL statement, a COBOL program named PGM1 in library LIB1 will be called.

   EXEC SQL
   DECLARE PROC1 PROCEDURE
           (CHAR(10), CHAR(10))
           EXTERNAL NAME LIB1.PGM1
           LANGUAGE COBOL GENERAL;

   EXEC SQL
   CALL PROC1 ('FIRSTNAME ','LASTNAME  ');

¹ When the language type is REXX, all parameters must be input parameters.