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

Start of changeIf a distinct type or array type is referenced, the privileges held by the authorization ID of the statement must include at least one of the following: End of change

  • Start of changeFor each distinct type or array type identified in the statement:
    • Start of changeThe USAGE privilege on the type, andEnd of change
    • Start of changeThe system authority *EXECUTE on the library containing the distinct type or array typeEnd of change
    End of change
  • Administrative authority

Start of changeTo replace an existing procedure, the privileges held by the authorization ID of the statement must include at least one of the following:End of change

  • The following system authorities:
    • The system authority of *OBJMGT on the program object associated with the procedure
    • All authorities needed to DROP the procedure
    • Start of changeThe system authority *READ to the SYSPROCS catalog view and SYSPARMS catalog tableEnd of change
  • Administrative authority

For information about 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

Read syntax diagramSkip visual syntax diagram
>>-CREATE--+------------+--PROCEDURE--procedure-name------------>
           '-OR REPLACE-'                              

>--+-----------------------------------------+--option-list----><
   '-(--+-------------------------------+--)-'                
        | .-,-------------------------. |                     
        | V                           | |                     
        '-----parameter-declaration---+-'                     


Read syntax diagramSkip visual syntax diagram
parameter-declaration

   .-IN----.                                                                                 
|--+-------+--+----------------+--data-type--+-----------------------+--+----------------+--|
   +-OUT---+  '-parameter-name-'             '-AS -+-LOCATOR-------+-'  '-default-clause-'   
   '-INOUT-'                                       '-XML-cast-type-'                         

data-type

|--+-built-in-type------+---------------------------------------|
   +-distinct-type-name-+   
   '-array-type-name----'   

XML-cast-type

                      .-(--1--)-------.                                                                       
|--+-+-+-CHARACTER-+--+---------------+--+--------------+-----------------------------------------------+-+--|
   | | '-CHAR------'  '-(--integer--)-'  '-ccsid-clause-'                                               | |   
   | +-+-+-CHARACTER-+--VARYING-+--(--integer--)--+--------------+--------------------------------------+ |   
   | | | '-CHAR------'          |                 '-ccsid-clause-'                                      | |   
   | | '-VARCHAR----------------'                                                                       | |   
   | |                                          .-(--1M--)-------------.                                | |   
   | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+--------------+--+---------+-' |   
   |       | '-CHAR------'               |      '-(--integer--+---+--)-'  '-ccsid-clause-'  '-LOCATOR-'   |   
   |       '-CLOB------------------------'                    +-K-+                                       |   
   |                                                          +-M-+                                       |   
   |                                                          '-G-'                                       |   
   |                .-(--1--)-------.                                                                     |   
   +-+---GRAPHIC----+---------------+--+--------------+--------------------+------------------------------+   
   | |              '-(--integer--)-'  '-ccsid-clause-'                    |                              |   
   | +-+-GRAPHIC VARYING-+--(--integer--)--+--------------+----------------+                              |   
   | | '-VARGRAPHIC------'                 '-ccsid-clause-'                |                              |   
   | |             .-(--1M--)-------------.                                |                              |   
   | '---DBCLOB----+----------------------+--+--------------+--+---------+-'                              |   
   |               '-(--integer--+---+--)-'  '-ccsid-clause-'  '-LOCATOR-'                                |   
   |                             +-K-+                                                                    |   
   |                             +-M-+                                                                    |   
   |                             '-G-'                                                                    |   
   |             .-(--1--)-------.                                                                        |   
   '-+-+-BINARY--+---------------+---------+------------------------------+-------------------------------'   
     | |         '-(--integer--)-'         |                              |                                   
     | '-+-BINARY VARYING-+--(--integer--)-'                              |                                   
     |   '-VARBINARY------'                                               |                                   
     |                              .-(--1M--)-------------.              |                                   
     '---+-BLOB----------------+----+----------------------+--+---------+-'                                   
         '-BINARY LARGE OBJECT-'    '-(--integer--+---+--)-'  '-LOCATOR-'                                     
                                                  +-K-+                                                       
                                                  +-M-+                                                       
                                                  '-G-'                                                       

default-clause

|--DEFAULT--+-NULL-------------+--------------------------------|
            +-constant---------+   
            +-special-register-+   
            +-global-variable--+   
            '-(--expression--)-'   


Read syntax diagramSkip visual syntax diagram
option-list

                             (1)   
|--+-----------------------+------------------------------------>
   '-LANGUAGE--+-C-------+-'       
               +-C++-----+         
               +-CL------+         
               +-COBOL---+         
               +-COBOLLE-+         
               +-JAVA----+         
               +-PLI-----+         
               +-REXX----+         
               +-RPG-----+         
               '-RPGLE---'         

   .-PARAMETER STYLE SQL--------------------.   
>--+----------------------------------------+------------------->
   '-+-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 .  .-INHERIT SPECIAL REGISTERS-.   
>--+----------------------+--+---------------------------+------>

   .-DYNAMIC RESULT SETS--0-------.  .-NO DBINFO-.   
>--+------------------------------+--+-----------+-------------->
   '-DYNAMIC RESULT SETS--integer-'  '-DBINFO----'   

                            .-FENCED-----.   
>--+---------------------+--+------------+---------------------->
   +-DISALLOW DEBUG MODE-+  '-NOT FENCED-'   
   +-ALLOW DEBUG MODE----+                   
   '-DISABLE DEBUG MODE--'                   

>--+-------------------+---------------------------------------->
   +-PROGRAM TYPE MAIN-+   
   '-PROGRAM TYPE SUB--'   

   .-EXTERNAL-----------------------------.   
>--+--------------------------------------+--------------------->
   '-EXTERNAL NAME--external-program-name-'   

                                .-OLD SAVEPOINT LEVEL-.   
>--+-------------------------+--+---------------------+--------->
   '-SPECIFIC--specific-name-'  '-NEW SAVEPOINT LEVEL-'   

   .-COMMIT ON RETURN NO--.   
>--+----------------------+-------------------------------------|
   '-COMMIT ON RETURN YES-'   
Notes:
  1. The optional clauses can be specified in a different order.
Read syntax diagramSkip visual syntax diagram
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--+---------+-'                                                                           |   
   +---ROWID----------------------------------------------------------------------------------------------+   
   '---XML------------------------------------------------------------------------------------------------'   


Read syntax diagramSkip visual syntax diagram
ccsid-clause

|--CCSID--integer--+------------------+-------------------------|
                   '-normalize-clause-'   

normalize-clause

   .-NOT NORMALIZED-.   
|--+-NORMALIZED-----+-------------------------------------------|

Description

Start of changeOR REPLACEEnd of change
Start of changeSpecifies to replace the definition for the procedure if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog with the exception that privileges that were granted on the procedure are not affected. This option is ignored if a definition for the procedure does not exist at the current server. To replace an existing procedure, the specific-name and procedure-name of the new definition must be the same as the specific-name and procedure-name of the old definition, or the signature of the new definition must match the signature of the old definition. Otherwise, a new procedure is created.End of change
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 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 The default is IN.
OUT
Identifies the parameter as an output parameter that is returned by the procedure.

A DataLink or a distinct type based on a DataLink cannot be specified as an output parameter.

INOUT
Identifies the parameter as both an input and output parameter for the procedure. Start of changeIf an INOUT parameter is defined with a default and the default is used when calling the procedure, no value for the parameter is returned.End of change

A DataLink or a distinct type based on a DataLink cannot 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.
built-in-type
Specifies a built-in data type. For a more complete description of each built-in data type, see CREATE TABLE.
distinct-type-name
Specifies a distinct type. Any length, precision, scale, or encoding scheme attributes for the parameter are those of the source type of the distinct type as specified using CREATE TYPE (Distinct).

If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

Start of changearray-type-nameEnd of change
Start of changeSpecifies an array type. Array types are only supported for LANGUAGE JAVA. To use an array type as a parameter for a Java™ stored procedure, the parameter style must be JAVA.

If the name of the array type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

End of change

If a CCSID is specified, the parameter will be converted to that CCSID before 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.

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 topic collection. Built-in data type specifications can be specified if they correspond to the language that is used to write the procedure.

Start of changeAny parameter that has an XML type must specify either the XML-cast-type clause or the AS LOCATOR clause.End of change

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.
Start of changeAS XML-cast-typeEnd of change
Start of changeSpecifies 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.

Start of changeIf 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 procedure 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.End of change

End of change
Start of changedefaultEnd of change
Start of changeSpecifies a default value for the parameter. The default can be a constant, a special register, a global variable, an expression, or the keyword NULL. The expression is any expression defined in Expressions, that does not include an aggregate function or column name. If a default value is not specified, the parameter has no default and cannot be omitted on invocation of the procedure. The maximum length of the expression string is 64K.

The default expression must not modify SQL data. The expression must be assignment compatible to the parameter data type. All objects referenced in a default expression must exist when the procedure is created. When the procedure is called, the default will be evaluated using the authority of the invoker.

Any comma in the default expression that is intended as a separator of numeric constants in a list must be followed by a space.

A default cannot be specified:
  • for an OUT parameter.
  • for a parameter of type array.
End of change
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.
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 to pass 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(1000) output parameter for the message text.
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.
These parameters are passed according to the specified LANGUAGE. For example, if the language is C or C++, the VARCHAR parameters are passed as NUL-terminated strings. 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.

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 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 topic collection.

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 topic collection. For Java routines, see the IBM Developer Kit for Java topic collection.

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. The default is NOT DETERMINISTIC.
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. The default is MODIFIES SQL DATA. Start of changeThis option is ignored for parameter default expressions.End of change For the classification of each statement, see Characteristics of SQL statements.
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, CONTAINS SQL, or NO SQL.
CONTAINS SQL
Specifies that the procedure can only execute statements with a data access classification of CONTAINS SQL or NO SQL.
NO SQL
Specifies that the procedure can execute only SQL statements with a data access classification of NO SQL.
CALLED ON NULL INPUT
Specifies that the procedure is to be called if any or all argument values are null. This specification means that the procedure must be coded to test for null argument values.
INHERIT SPECIAL REGISTERS
Specifies that existing values of special registers are inherited upon entry to the procedure.
FENCED or NOT FENCED
This parameter is allowed for compatibility with other products and is not used by DB2® for i.
DYNAMIC RESULT SETS integer
Specifies the maximum number of result sets that can be returned from the procedure. The minimum value for integer is zero and the maximum value is 32768.

If no DYNAMIC RESULT SETS clause is specified, result sets are returned for all cursors that remain open when the procedure ends.

Result sets are returned in the order in which the corresponding cursors are opened, unless a SET RESULT SETS statement is executed in the procedure. If the number of cursors that are still open for result sets when the procedure ends exceeds the maximum number specified on the DYNAMIC RESULT SETS clause, a warning is returned on the CALL statement and the number of result sets specified on the DYNAMIC RESULT SETS clause is 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 before returning from the procedure, the result set starts with the 6th row of the result set.

Start of changeCursor result sets are only returned if the external program does not have an attribute of ACTGRP(*NEW).End of change

For more information about result sets, see SET RESULT SETS.

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.
PROGRAM TYPE
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).
SUB
Specifies that the procedure executes as a procedure in a service program. The external program must be a *SRVPGM object.
MAIN
Specifies that the routine executes as the main entry point in a program. The external program must be a *PGM object.
DBINFO
Specifies whether additional status information is passed to the procedure when it is called. The default is NO DBINFO.
NO DBINFO
Additional information is not passed.
DBINFO
An additional argument is passed when the procedure is called.

The argument is a structure that contains information such as the name of the current server, the application run-time authorization ID, and identification of the version and release of the database manager that called the procedure. See Table 1 for further details. 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 SQL.

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
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 8 bytes of reserved space.

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
Specifies that the CREATE PROCEDURE statement is being used to define a new procedure based on code written in an external programming language.
If NAME clause is not specified, "NAME procedure-name" is assumed. In this case, procedure-name must not be longer than 8 characters. The NAME clause is required for a LANGUAGE JAVA procedure since the default name is not valid for a Java procedure.
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 at the time the procedure is called.
  • *LIBL will be used to search for the program or service program at the time Start of changeCOMMENT, GRANT, LABEL, or REVOKEEnd of change operations 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 created, but it must exist at the time the procedure is called.

Start of changeThe SET TRANSACTION statement is 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.End of change

SPECIFIC specific-name
Specifies a unique name for the function. For more information on specific names, see Specifying a specific name for a procedure.
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.2

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.

Start of changeSET OPTION-statementEnd of change
Start of changeSpecifies the options that will be used for parameter defaults. The default values for the options depend on the options in effect at create time. For more information, see SET OPTION.

The following options are used when processing default value expressions: ALWCPYDTA, CONACC, DATFMT, DATSEP, DECFLTRND, DECMPT, DECRESULT, DFTRDBCOL, LANGID, SQLCURRULE, SQLPATH, SRTSEQ, TGTRLS, TIMFMT, and TIMSEP. The options CNULRQD, CNULIGN, COMPILEOPT, EXTIND, NAMING, and SQLCA are not allowed in the CREATE PROCEDURE statement. Other options are accepted but will be ignored.

End of change

Notes

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

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

Start of change
REPLACE rules: When an external procedure is recreated by REPLACE:
  • Any existing comment or label is discarded.
  • If a different external program is specified:
    • Authorized users are not copied to the new program.
    • Journal auditing is not changed.
  • Otherwise:
    • Authorized users are maintained. The object owner will not change.
    • Current journal auditing is not changed.
End of change

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.

Start of changeCreating 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 or *SRVPGM object is saved and then restored to this or another system, the attributes are used to update the catalogs.End of change

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.

    If system naming is specified and the external program name is not qualified, the external program must be found in the library list.

  • The external program must be an ILE *PGM or *SRVPGM object.
  • The external program must not already contain attributes for 32 routines.

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

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

Start of change

Setting of the default value: Parameters of a procedure that are defined with a default value are set to their default value when the procedure is invoked, but only if a value is not supplied for the corresponding argument, or the argument is specified as DEFAULT.

End of change

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 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.
  • 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.
  • The keywords PARAMETER STYLE DB2SQL can be used as a synonym for PARAMETER STYLE SQL.

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
Parent topic: Statements
1 When the language type is REXX, all parameters must be input parameters.
2 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.