CREATE PROCEDURE (External)

The CREATE PROCEDURE (External) statement defines an external procedure at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following:

For the SYSPROCS catalog view and SYSPARMS catalog table:
- The INSERT privilege on the table, and
- The system authority *EXECUTE on library QSYS2
Administrative authority

If the external program or service program exists, the privileges held by the authorization ID of the statement must include at least one of the following:

For the external program or service program that is referenced in the SQL statement:
- The system authority *EXECUTE on the library that contains the external program or service program.
- The system authority *EXECUTE on the external program or service program, and
- The system authority *CHANGE on the program or service program. The system needs this authority to update the program or service program object to contain the information necessary to save/restore the procedure to another system. If user does not have this authority, the procedure is still created, but the program or service program object is not updated.
Administrative Authority

If a distinct type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following:

For each distinct type identified in the statement:
- The USAGE privilege on the distinct type, and
- The system authority *EXECUTE on the library containing the distinct type
Administrative authority

For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure and Corresponding System Authorities When Checking Privileges to a Distinct Type.

Syntax

>>-CREATE PROCEDURE--procedure-name----------------------------->
 
>--+-----------------------------------------+--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 DB2SQL-------------+-'
     +-PARAMETER STYLE GENERAL------------+
     +-PARAMETER STYLE GENERAL WITH NULLS-+
     +-PARAMETER STYLE JAVA---------------+
     '-PARAMETER STYLE DB2GENERAL---------'
 
   .-NOT DETERMINISTIC-.  .-MODIFIES SQL DATA-.
>--+-------------------+--+-------------------+----------------->
   '-DETERMINISTIC-----'  +-READS SQL DATA----+
                          +-CONTAINS SQL------+
                          '-NO SQL------------'
 
   .-CALLED ON NULL INPUT-.  .-DYNAMIC RESULT SETS--0-------.
>--+----------------------+--+------------------------------+--->
                             '-DYNAMIC RESULT SETS--integer-'
 
   .-NO DBINFO-.                           .-FENCED-----.
>--+-----------+--+---------------------+--+------------+------->
   '-DBINFO----'  +-DISALLOW DEBUG MODE-+  '-NOT FENCED-'
                  +-ALLOW DEBUG MODE----+
                  '-DISABLE DEBUG MODE--'
 
   .-PROGRAM TYPE MAIN-.
>--+-------------------+---------------------------------------->
 
   .-EXTERNAL-----------------------------.
>--+--------------------------------------+--------------------->
   '-EXTERNAL NAME--external-program-name-'
 
                                .-OLD SAVEPOINT LEVEL-.
>--+-------------------------+--+---------------------+--------->
   '-SPECIFIC--specific-name-'  '-NEW SAVEPOINT LEVEL-'
 
   .-COMMIT ON RETURN NO--.
>--+----------------------+-------------------------------------|
   '-COMMIT ON RETURN YES-'

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-------------------+----------------------------------------------------------------+
   | |       .-(--0--)-.      |                                                                |
   | +-TIME--+---------+------+                                                                |
   | |            .-(--6--)-. |                                                                |
   | '-TIMESTAMP--+---------+-'                                                                |
   '---ROWID-----------------------------------------------------------------------------------'
 
ccsid-clause:
 
                   .-NOT NORMALIZED-.
|--CCSID--integer--+----------------+---------------------------|
                   '-NORMALIZED-----'

Description

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.⁶⁶

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:

The first N parameters are the parameters that are specified on the CREATE 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 procedure.
A VARCHAR(517) input parameter for the fully qualified procedure name.
A VARCHAR(128) input parameter for the specific name.
A VARCHAR(70) output parameter for the message text.

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.

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).

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.

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

Specifies that the procedure executes as a main routine.

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.⁶⁷

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.

Notes

General considerations for defining procedures: See CREATE PROCEDURE for general information on defining procedures.

Language considerations: For information needed to create the programs for a procedure, see the Embedded SQL Programming book.

Owner privileges: The owner is authorized to call the procedure (EXECUTE) and grant others the privilege to call the procedure. See GRANT (Function or Procedure Privileges). For more information on ownership of the object, see Authorization, privileges and object ownership.

Error handling considerations: Values of arguments passed to a procedure which correspond to OUT parameters are undefined and those which correspond to INOUT parameters are unchanged when an error is returned by the procedure.

Creating the procedure: When an external procedure associated with an ILE external program or service program is created, an attempt is made to save the procedure's attributes in the associated program or service program object. If the *PGM object is saved and then restored to this or another system, the catalogs are automatically updated with those attributes.

The attributes can be saved for external procedures subject to the following restrictions:

The external program library must not be QSYS.
The external program must exist when the CREATE PROCEDURE statement is issued.
The external program must be an ILE *PGM or *SRVPGM object.

If the object cannot be updated, the procedure will still be created.

During restore of the procedure:

If the specific name was specified when the procedure was originally created and it is not unique, an error is issued.
If the specific name was not specified, a unique name is generated if necessary.
If the same procedure name and number of parameters already exists,
- If the external program name or service program name is the same as the one registered in the catalog, the procedure information in the catalog will be replaced.
- Otherwise, the procedure cannot be registered, and an error is issued.

Invoking the procedure: If a DECLARE PROCEDURE statement defines a procedure with the same name as a created procedure, and a static CALL statement where the procedure name is not identified by a variable is executed from the same source program, the attributes from the DECLARE PROCEDURE statement will be used rather than the attributes from the CREATE PROCEDURE statement.

The CREATE PROCEDURE statement applies to static and dynamic CALL statements as well as to a CALL statement where the procedure name is identified by a variable.

When an external procedure is invoked, it runs in whatever activation group was specified when the external program or service program was created. However, ACTGRP(*CALLER) should normally be used so that the procedure runs in the same activation group as the calling program.

Notes for Java procedures: To be able to run Java procedures, you must have the IBM Developer Kit for Java installed on your system. Otherwise, an SQLCODE of -443 will be returned and a CPDB521 message will be placed in the job log.

If an error occurs while running a Java procedure, an SQLCODE of -443 will be returned. Depending on the error, other messages may exist in the job log of the job where the procedure was run.

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.
DYNAMIC RESULT SET, RESULT SETS, and RESULT SET may be used as synonyms for DYNAMIC RESULT SETS.
The keywords PARAMETER STYLE in the PARAMETER STYLE clause are optional.

Example

Example 1: Create the procedure definition for a procedure, written in Java, that is passed a part number and returns the cost of the part and the quantity that are currently available.

   CREATE PROCEDURE PARTS_ON_HAND (IN  PARTNUM  INTEGER, 
                                   OUT COST     DECIMAL(7,2), 
                                   OUT QUANTITY INTEGER)
          LANGUAGE JAVA
          PARAMETER STYLE JAVA
          EXTERNAL NAME 'parts.onhand'

Example 2: Create the procedure definition for a procedure, written in C, that is passed an assembly number and returns the number of parts that make up the assembly, total part cost and a result set that lists the part numbers, quantity and unit cost of each part.

   CREATE PROCEDURE ASSEMBLY_PARTS (IN  ASSEMBLY_NUM  INTEGER, 
                                    OUT NUM_PARTS     INTEGER, 
                                    OUT COST DOUBLE)
          LANGUAGE C
          PARAMETER STYLE GENERAL 
          DYNAMIC RESULT SETS 1 
          FENCED
          EXTERNAL NAME ASSEMBLY

66.

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

67.

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.

[ Top of Page | Previous Page | Next Page | Contents | Index ]