- LANGUAGE
- Specifies the language that the external program or service program
is written in. The language clause is required if the external program is
a REXX procedure.
- 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.
- 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(TM) methods.
PARAMETER STYLE DB2GENERAL
can only be specified with LANGUAGE JAVA. For details on passing parameters
in JAVA, see the IBM(R) Developer Kit for Java.
- 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 CREATE
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 ISO/IEC FCD 9075-13:2003, Information technology - Database languages - SQL - Part 13: Java Routines and Types (SQL/JRT) specification. INOUT and OUT
parameters will be passed as single entry arrays to facilitate returning values.
PARAMETER STYLE JAVA can only be specified with LANGUAGE JAVA. For increased
portability, you should write Java procedures that use the PARAMETER
STYLE JAVA conventions. For details on passing parameters in JAVA, see the IBM Developer Kit for Java book.
Note that the language of the external procedure 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.
- EXTERNAL NAME external-program-name
- Specifies the program or service program that will be executed when
the procedure is called by the CALL statement. The program name must identify
a program or service program that exists at the application server at the time the
procedure is called. If the naming option is *SYS and the name is not qualified:
- The current path will be used to search for the program or service program
at the time the procedure is called.
- *LIBL will be used to search for the program or service program at the
time grants or revokes are performed on the procedure.
The validity of the name is checked at the application server. If the format
of the name is not correct, an error is returned.
The external program
or service program need not exist at the time the procedure is altered, but
it must exist at the time the procedure is called.
CONNECT, SET CONNECTION,
RELEASE, DISCONNECT, and SET TRANSACTION statements are not allowed in a procedure
that is running on a remote application server. COMMIT and ROLLBACK statements are
not allowed in an ATOMIC SQL procedure or in a procedure that is running on
a connection to a remote application server.
- NOT DETERMINISTIC or 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 the classification of SQL statements that this procedure,
or any routine called by this procedure, can execute. The database manager
verifies that the SQL statements issued by the procedure and all routines
called by the procedure are consistent with this specification. For the classification
of each statement, see SQL statement data access indication in routines.
- 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 the procedure can execute statements with a data access
classification of READS SQL DATA or CONTAINS SQL.
- CONTAINS SQL
- Specifies that the procedure can only execute statements with a data
access classification of CONTAINS SQL.
- NO SQL
- The function does not execute SQL statements.
- CALLED ON NULL INPUT
- Specifies that the procedure will be called if any, or all, parameter
values are null.
- INHERIT SPECIAL REGISTERS
- Specifies that existing values of special registers are inherited upon
entry to the procedure.
- 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 result sets returned
is the minimum of the number of results sets specified on this keyword and
the SET RESULT SETS statement. If the SET RESULT SETS statement specifies
a number larger than the maximum number of result sets, a warning is returned.
Note that any result sets from cursors that have a RETURN TO CLIENT attribute
are included in the number of result sets of the outermost procedure.
The result sets are scrollable if a cursor is used to return a result set
and the cursor is scrollable. If a cursor is used to return a result set,
the result set starts with the current position. Thus, if 5 FETCH NEXT operations
have been performed prior to returning from the procedure, the result set
will start with the 6th row of the result set.
Result sets are only
returned if both the following are true:
- 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, and
- the external program does not have an attribute of ACTGRP(*NEW)
and the result set is not an array result set.
For more information about result sets see SET RESULT SETS.
- DBINFO
- Specifies whether or not the procedure requires the database information
be passed.
- DBINFO
- Specifies that the database manager should pass a structure containing
status information to the procedure. Table 45 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 45. 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. |
- NO DBINFO
- Specifies that the procedure does not require the database information
to be passed.
- FENCED or NOT FENCED
- This parameter is allowed for compatibility with other products and
is not used by DB2 UDB for iSeries.
- DISALLOW DEBUG MODE, ALLOW DEBUG MODE, or DISABLE
DEBUG MODE
- Indicates whether the procedure can be debugged by the Unified Debugger. If DEBUG MODE is not specified, the procedure will be created with
the debug mode specified by the CURRENT DEBUG MODE special register.
DEBUG
MODE can only be specified with LANGUAGE JAVA procedures.
- DISALLOW DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISALLOW, the procedure can be subsequently
altered to change the debug mode attribute.
- ALLOW DEBUG MODE
- The procedure can be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is ALLOW, the procedure can be subsequently altered
to change the debug mode attribute.
- DISABLE DEBUG MODE
- The procedure cannot be debugged by the Unified Debugger. When the DEBUG MODE
attribute of the procedure is DISABLE, the procedure cannot be subsequently
altered to change the debug mode attribute.
- OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL
- Specifies whether a new savepoint level is to be created on entry to
the procedure.
- OLD SAVEPOINT LEVEL
- A new savepoint level is not created. Any SAVEPOINT statements issued
within the procedure with OLD SAVEPOINT LEVEL implicitly or explicitly specified
on the SAVEPOINT statement are created at the same savepoint level as the
caller of the procedure.
- NEW SAVEPOINT LEVEL
- A new savepoint level is created on entry to the procedure. Any savepoints
set within the procedure are created at a savepoint level that is nested deeper
than the level at which this procedure was invoked. Therefore, the name of
any new savepoint within the procedure will not conflict with any existing
savepoint levels (such as the savepoint level of the calling program) with
the same name.
- COMMIT ON RETURN
- Specifies whether the database manager commits the transaction immediately
on return from the procedure.
- NO
- The database manager does not issue a commit when the procedure returns.
- YES
- The database manager issues a commit when the procedure returns successfully.
If the procedure returns with an error, a commit is not issued.
The commit
operation includes the work that is performed by the calling application process
and the procedure.
If the procedure returns result sets, the cursors
that are associated with must have been defined as WITH HOLD to be usable
after the commit.