- procedure-name
- Names the procedure. The combination of name, schema name, the number
of parameters must not identify a procedure that exists at the current server.
For SQL naming, the procedure will be created in the schema specified by the
implicit or explicit qualifier.
For system naming, the procedure will
be created in the schema specified by the qualifier. If no qualifier is specified:
- If the value of the CURRENT SCHEMA special register is *LIBL, the procedure
will be created in the current library (*CURLIB).
- Otherwise, the procedure will be created in the current schema.
- (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 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.66
- 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 can
be a built-in data type or a distinct type.
- built-in-type
- Specifies a built-in data type. For a more complete description of each
built-in data type, see CREATE TABLE. Some data types are not supported
in all languages. For details on the mapping between the SQL data types and
host language data types, see Embedded SQL Programming book. Built-in data type specifications can be specified if they correspond
to the language that is used to write the procedure.
- distinct-type-name
- Specifies a user-defined distinct type. The length, precision, or scale
attributes for the parameter are those of the source type of the distinct
type (those specified on CREATE DISTINCT TYPE). For more information on creating
a distinct type, see CREATE DISTINCT TYPE.
If the name of the distinct type is
unqualified, the database manager resolves the schema name by searching the
schemas in the SQL path.
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 invoked.
- 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.
- 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.
If LANGUAGE is not specified, the LANGUAGE is determined
from the attribute information associated with the external program or service
program at the time the procedure is created. If the attribute information
associated with the program or service program does not identify a recognizable
language or the program or service program cannot be found, 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.
- 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.
If external-program-name is not specified, the external program name is assumed to be the same
as the procedure name.
The external program or service program need
not exist at the time the procedure is created, 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.
- 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. 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.
- SPECIFIC specific-name
- Provides a unique name for the procedure. The name is implicitly or
explicitly qualified with a schema name. The name, including the schema name,
must not identify the specific name of another procedure or procedure that
exists at the current server. If unqualified, the implicit qualifier is the
same as the qualifier of the procedure name. If qualified, the qualifier must
be the same as the qualifier of the procedure name.
If specific-name is not specified, it is the same as the procedure name. If a function
or procedure with that specific name already exists, a unique name is generated
similar to the rules used to generate unique table names.
- 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 procedure is to be invoked, if any, or all, argument
values are null, making the procedure responsible for testing for null argument
values. The procedure can return a null or nonnull value.
- INHERIT SPECIAL REGISTERS
- Specifies that existing values of special registers are inherited upon
entry to the procedure.
- DISALLOW DEBUG MODE, ALLOW DEBUG MODE, or DISABLE DEBUG MODE
- Indicates whether the procedure is created so it 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.
- 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.
- 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 or PROGRAM TYPE MAIN
- 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 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 51 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 51. 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.
- 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. This is the default.
- 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 set within the procedure will not conflict with any existing
savepoints set in higher savepoint levels (such as the savepoint level of
the calling program or service 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. NO is
the default.
- YES
- The database manager issues a commit if 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.67
If the procedure returns result
sets, the cursors that are associated with the result sets must have been
defined as WITH HOLD to be usable after the commit.
When the language type is REXX, all parameters must be input
parameters.
If the external program or service program was created
with ACTGRP(*NEW) and the job commitment definition is not used, the work
that is performed in the procedure will be committed or rolled back as a result
of the activation group ending.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.