The DECLARE PROCEDURE statement defines an external procedure.
This statement can only be embedded in an application program. It is not
an executable statement. It must not be specified in REXX.
None.
>>-DECLARE--procedure-name--PROCEDURE--------------------------->
>--+-----------------------------------------+--option-list----><
'-(--+-------------------------------+--)-'
| .-,-------------------------. |
| V | |
'-----parameter-declaration---+-'
parameter-declaration:
.-IN----.
|--+-------+--+----------------+--data-type--+------------+-----|
+-OUT---+ '-parameter-name-' '-AS LOCATOR-'
'-INOUT-'
data-type:
|--+-built-in-type------+---------------------------------------|
'-distinct-type-name-'
option-list:
(1)
|--+-----------------------+------------------------------------>
'-LANGUAGE--+-C-------+-'
+-C++-----+
+-CL------+
+-COBOL---+
+-COBOLLE-+
+-FORTRAN-+
+-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-----. .-PROGRAM TYPE MAIN-.
>--+-----------+--+------------+--+-------------------+--------->
'-DBINFO----' '-NOT FENCED-'
.-EXTERNAL-----------------------------.
>--+--------------------------------------+--------------------->
'-EXTERNAL NAME--external-program-name-'
>--+-------------------------+----------------------------------|
'-SPECIFIC--specific-name-'
Notes:
- The optional clauses can be specified in a different order.
built-in-type:
|--+-+---SMALLINT---+--------------------------------------------------------------------------+--|
| +-+-INTEGER-+--+ |
| | '-INT-----' | |
| '---BIGINT-----' |
| .-(5,0)------------------------. |
+-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+
| | '-DEC-----' | | .-,0--------. | |
| '-NUMERIC-----' '-(--integer--+-----------+--)-' |
| '-, integer-' |
| .-(--53--)------. |
+-+-FLOAT--+---------------+-+--------------------------------------------------------------+
| | '-(--integer--)-' | |
| +-REAL---------------------+ |
| | .-PRECISION-. | |
| '-DOUBLE--+-----------+----' |
| .-(--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--)-------. |
+-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+
| | | '-(--integer--)-' | | |
| | '-+-BINARY VARYING-+--(--integer--)-' | |
| | '-VARBINARY------' | |
| | .-(--1M--)-------------. | |
| '---+-BLOB----------------+----+----------------------+-' |
| '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' |
| +-K-+ |
| +-M-+ |
| '-G-' |
+-+-DATE------+-----------------------------------------------------------------------------+
| +-TIME------+ |
| '-TIMESTAMP-' |
| .-(--200--)-----. |
+---DATALINK--+---------------+--+--------------+-------------------------------------------+
| '-(--integer--)-' '-ccsid-clause-' |
'---ROWID-----------------------------------------------------------------------------------'
ccsid-clause:
.-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
'-NORMALIZED-----'
- 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.69
- 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 book.
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.
- 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 data
type or a distinct type based on a LOB data type. If AS LOCATOR is specified,
FOR SBCS DATA or FOR MIXED DATA must not be specified.
- 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.
Result sets are only returned if the procedure
is directly called or if the procedure is a RETURN TO CLIENT procedure and
is indirectly called from ODBC, JDBC, OLE DB, .NET, the SQL Call Level Interface,
or the iSeries Access Family Optimized SQL API. 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.
- FORTRAN
- The external program is written in FORTRAN.
- 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.
- 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.
- CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA, or NO SQL
- Specifies which SQL statements, if any, may be executed in the procedure
or any routine called from this procedure. See Appendix B. Characteristics of SQL statements for a
detailed list of the SQL statements that can be executed under each data access
indication.
- 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.
- READS SQL DATA
- Specifies that SQL statements that do not modify SQL data can be included
in the procedure.
- MODIFIES SQL DATA
- Specifies that the procedure can execute any SQL statement except statements
that are not supported in procedures.
- 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 UDB for iSeries.
- PROGRAM TYPE MAIN
- Specifies that the procedure executes as a main routine.
- DBINFO
- Specifies that the database manager should pass a structure containing
status information to the procedure. Table 54 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 54. 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.
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.
- 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:
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
book.
- 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.
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 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 book.
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
book.
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 the SQL Programming book. For Java routines, see the IBM Developer
Kit for Java.
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 and NOT NULL CALL can be used as synonyms for CALLED
ON NULL INPUT and RETURNS NULL 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.
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.
[ Top of Page | Previous Page | Next Page | Contents |
Index ]