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
>>-DECLARE--procedure-name--PROCEDURE---------------------------> >--+-----------------------------------------+--option-list---->< '-(--+-------------------------------+--)-' | .-,-------------------------. | | V | | '-----parameter-declaration---+-'
parameter-declaration .-IN----. |--+-------+--+----------------+--data-type--+-----------------------+--| +-OUT---+ '-parameter-name-' '-AS -+-LOCATOR-------+-' '-INOUT-' '-XML-cast-type-' data-type |--+-built-in-type------+---------------------------------------| '-distinct-type-name-' XML-cast-type .-(--1--)-------. |--+-+-CHARACTER-+--+---------------+-----------------------------------------------+--| | '-CHAR------' '-(--integer--)-' | +-+-+-CHARACTER-+--VARYING-+--(--integer--)--------------------------------------+ | | '-CHAR------' | | | '-VARCHAR----------------' | | .-(--1M--)-------------. | +-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+---------+-+ | | '-CHAR------' | '-(--integer--+---+--)-' '-LOCATOR-' | | '-CLOB------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +---GRAPHIC----+---------------+-------------------------------------------------+ | '-(--integer--)-' | +-+-GRAPHIC VARYING-+--(--integer--)---------------------------------------------+ | '-VARGRAPHIC------' | | .-(--1M--)-------------. | '---DBCLOB----+----------------------+--+---------+------------------------------' '-(--integer--+---+--)-' '-LOCATOR-' +-K-+ +-M-+ '-G-'
option-list (1) |--+-----------------------+------------------------------------> '-LANGUAGE--+-C-------+-' +-C++-----+ +-CL------+ +-COBOL---+ +-COBOLLE-+ +-JAVA----+ +-PLI-----+ +-REXX----+ +-RPG-----+ '-RPGLE---' .-PARAMETER STYLE SQL---------------------. >--+-----------------------------------------+------------------> '-PARAMETER STYLE--+-DB2GENERAL---------+-' +-DB2SQL-------------+ +-GENERAL------------+ +-GENERAL WITH NULLS-+ '-JAVA---------------' .-NOT DETERMINISTIC-. .-MODIFIES SQL DATA-. >--+-------------------+--+-------------------+-----------------> '-DETERMINISTIC-----' +-NO SQL------------+ +-CONTAINS SQL------+ '-READS SQL DATA----' .-CALLED ON NULL INPUT . .-DYNAMIC RESULT SETS--0-------. >--+----------------------+--+------------------------------+---> '-DYNAMIC RESULT SETS--integer-' .-NO DBINFO-. .-FENCED-----. >--+-----------+--+------------+--+-------------------+---------> '-DBINFO----' '-NOT FENCED-' +-PROGRAM TYPE MAIN-+ '-PROGRAM TYPE SUB--' .-EXTERNAL-----------------------------. >--+--------------------------------------+---------------------> '-EXTERNAL NAME--external-program-name-' >--+-------------------------+----------------------------------| '-SPECIFIC--specific-name-'
- The optional clauses can be specified in a different order.
built-in-type |--+-+---SMALLINT---+-------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+----------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+-----------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+---------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID----------------------------------------------------------------------------------------------+ '---XML------------------------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
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 DECLARE PROCEDURE depends on the language and the parameter style:
- If PARAMETER STYLE GENERAL is specified, in C and C++, the maximum is 1024. Otherwise, the maximum is 255.
- If PARAMETER STYLE GENERAL WITH NULLS is specified, in C and C++, the maximum is 1023. Otherwise, the maximum is 254.
- If PARAMETER STYLE SQL or PARAMETER STYLE DB2SQL is specified, in C and C++, the maximum is 508. Otherwise, the maximum is 90.
- If PARAMETER STYLE JAVA or PARAMETER STYLE DB2GENERAL is specified, the maximum is 90.
The maximum number of parameters is also limited by the maximum number of parameters allowed by the licensed program used to compile the external program or service program.
- 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.1
- 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.
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.
- DB2SQL
- Specifies
that in addition to the parameters on the CALL statement, several
additional parameters are passed to the procedure. DB2SQL is identical
to the SQL parameter style, except that the following additional parameter
may be passed as the last parameter:
- A parameter for the dbinfo structure, if DBINFO was specified on the DECLARE PROCEDURE statement.
PARAMETER STYLE DB2SQL cannot be used with LANGUAGE JAVA.
- 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
INTEGERINTEGER
INTEGER
INTEGERINTEGER
INTEGER
INTEGERINTEGER
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
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.
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 ');