CREATE PROCEDURE (external)

The CREATE PROCEDURE statement defines an external stored 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 only if DYNAMICRULES run behavior is specified implicitly or explicitly.

Authorization

The privilege set that is defined below must include at least one of the following:

The CREATEIN privilege on the schema
SYSADM or SYSCTRL authority
System DBADM

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Start of change If the authorization ID that is used to create the procedure has installation SYSADM authority, the procedure is identified as system-defined procedure. End of change

When LANGUAGE is JAVA and a jar-name is specified in the EXTERNAL NAME clause, the privilege set must include USAGE on the JAR file, the Java archive file.

Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the plan or package. If the owner is a role, the implicit schema match does not apply and this role needs to include one of the previously listed conditions.

If the statement is dynamically prepared and is not running in a trusted context for which the ROLE AS OBJECT OWNER clause is specified, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. If the schema name is not the same as the SQL authorization ID of the process, one of the following conditions must be met:

The privilege set includes SYSADM or SYSCTRL authority.
The SQL authorization ID of the process has the CREATEIN privilege on the schema.

The authorization ID that is used to create the stored procedure must have authority to define programs that run in the specified WLM environment. In addition, if the stored procedure uses a distinct type as a parameter, this authorization ID must have the USAGE privilege on each distinct type that is a parameter.

Syntax


>>-CREATE PROCEDURE--procedure-name----------------------------->

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

>--| option-list |---------------------------------------------><

parameter-declaration:


   .-IN--------.                                           
>>-+-----------+--+----------------+--| parameter-type |-------><
   +-OUT-------+  '-parameter-name-'                       
   |       (1) |                                           
   '-INOUT-----'

Notes:

For a REXX stored procedure, only one parameter can have type OUT or INOUT. That parameter must be declared last.

parameter-type:


>>-+-data-type--+----------------+----------+------------------><
   |            |            (1) |          |   
   |            '-AS LOCATOR-----'          |   
   '-TABLE LIKE--+-table-name-+--AS LOCATOR-'   
                 '-view-name--'

Notes:

AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.

data-type:


>>-+-| built-in-type |--+--------------------------------------><
   '-distinct-type-name-'

built-in-type:

>>-+-+-SMALLINT----+-------------------------------------------------------------------------------------------------+-><
   | +-+-INTEGER-+-+                                                                                                 |   
   | | '-INT-----' |                                                                                                 |   
   | '-BIGINT------'                                                                                                 |   
   |              .-(5,0)--------------------.                                                                       |   
   +-+-DECIMAL-+--+--------------------------+-----------------------------------------------------------------------+   
   | +-DEC-----+  '-(integer-+-----------+-)-'                                                                       |   
   | '-NUMERIC-'             '-, integer-'                                                                           |   
   |          .-(53)------.                                                                                          |   
   +-+-FLOAT--+-----------+--+---------------------------------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                                                       |   
   | +-REAL------------------+                                                                                       |   
   | |         .-PRECISION-. |                                                                                       |   
   | '-DOUBLE--+-----------+-'                                                                                       |   
   |           .-(34)-.                                                                                              |   
   +-DECFLOAT--+------+----------------------------------------------------------------------------------------------+   
   |           '-(16)-'                                                                                              |   
   |                    .-(1)-------.                                                                                |   
   +-+-+-+-CHARACTER-+--+-----------+----------+----+--------------------+--+----------------------+---------------+-+   
   | | | '-CHAR------'  '-(integer)-'          |    '-CCSID--+-ASCII---+-'  '-FOR--+-SBCS--+--DATA-'               | |   
   | | '-+-+-CHARACTER-+--VARYING-+--(integer)-'             +-EBCDIC--+           +-MIXED-+                       | |   
   | |   | '-CHAR------'          |                          '-UNICODE-'           '-BIT---'                       | |   
   | |   '-VARCHAR----------------'                                                                                | |   
   | |                                  .-(1M)-------------.                                                       | |   
   | '-+-+-CHARACTER-+--LARGE OBJECT-+--+------------------+----+--------------------+--+----------------------+---' |   
   |   | '-CHAR------'               |  '-(integer-+---+-)-'    '-CCSID--+-ASCII---+-'  '-FOR--+-SBCS--+--DATA-'     |   
   |   '-CLOB------------------------'             +-K-+                 +-EBCDIC--+           '-MIXED-'             |   
   |                                               +-M-+                 '-UNICODE-'                                 |   
   |                                               '-G-'                                                             |   
   |            .-(1)-------.                                                                                        |   
   +-+-GRAPHIC--+-----------+-------+--+--------------------+--------------------------------------------------------+   
   | |          '-(integer)-'       |  '-CCSID--+-ASCII---+-'                                                        |   
   | +-VARGRAPHIC--(--integer--)----+           +-EBCDIC--+                                                          |   
   | |         .-(1M)-------------. |           '-UNICODE-'                                                          |   
   | '-DBCLOB--+------------------+-'                                                                                |   
   |           '-(integer-+---+-)-'                                                                                  |   
   |                      +-K-+                                                                                      |   
   |                      +-M-+                                                                                      |   
   |                      '-G-'                                                                                      |   
   |           .-(1)-------.                                                                                         |   
   +-+-BINARY--+-----------+-------------------------+---------------------------------------------------------------+   
   | |         '-(integer)-'                         |                                                               |   
   | +-+-BINARY VARYING-+-(integer)------------------+                                                               |   
   | | '-VARBINARY------'                            |                                                               |   
   | |                          .-(1M)-------------. |                                                               |   
   | '-+-BINARY LARGE OBJECT-+--+------------------+-'                                                               |   
   |   '-BLOB----------------'  '-(integer-+---+-)-'                                                                 |   
   |                                       +-K-+                                                                     |   
   |                                       +-M-+                                                                     |   
   |                                       '-G-'                                                                     |   
   +-+-DATE------------------------------------------------+---------------------------------------------------------+   
   | +-TIME------------------------------------------------+                                                         |   
   | |            .-(--6--)-------.  .-WITHOUT TIME ZONE-. |                                                         |   
   | '-TIMESTAMP--+---------------+--+-------------------+-'                                                         |   
   |              '-(--integer--)-'  '-WITH TIME ZONE----'                                                           |   
   '-ROWID-----------------------------------------------------------------------------------------------------------'

option-list: (The options can be specified in any order.)

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

>--+---------------------------------------------------+-------->
   |                .--------------------------------. |   
   |           (1)  V                                | |   
   '-PARAMETER--------+-CCSID--+-ASCII---+---------+-+-'   
                      |        +-EBCDIC--+         |       
                      |        '-UNICODE-'         |       
                      |         (2)                |       
                      '-VARCHAR------+-NULTERM---+-'       
                                     '-STRUCTURE-'         

>--EXTERNAL--+----------------------+--LANGUAGE--+-ASSEMBLE-+--->
             |                (3)   |            +-C--------+   
             '-NAME--+-string-----+-'            +-COBOL----+   
                     '-identifier-'              +-JAVA-----+   
                                                 +-PLI------+   
                                                 '-REXX-----'   

   .-MODIFIES SQL DATA-.   
>--+-------------------+---------------------------------------->
   +-READS SQL DATA----+   
   +-CONTAINS SQL------+   
   '-NO SQL------------'   

   .-PARAMETER STYLE SQL---------------------.   
>--+-----------------------------------------+------------------>
   '-PARAMETER STYLE--+-GENERAL------------+-'   
                      +-GENERAL WITH NULLS-+     
                      '-JAVA---------------'     

   .-NOT DETERMINISTIC-.  .-NO PACKAGE PATH------------.   
>--+-------------------+--+----------------------------+-------->
   '-DETERMINISTIC-----'  '-PACKAGE PATH--package-path-'   

   .-FENCED-.  .-NO DBINFO-.  .-NO COLLID-------------.   
>--+--------+--+-----------+--+-----------------------+--------->
               '-DBINFO----'  '-COLLID--collection-id-'   

>--+---------------------------------------+-------------------->
   '-WLM ENVIRONMENT--+-name-------------+-'   
                      '-(--name--,--*--)-'     

   .-ASUTIME NO LIMIT--------.  .-STAY RESIDENT NO--.   
>--+-------------------------+--+-------------------+----------->
   '-ASUTIME--LIMIT--integer-'  '-STAY RESIDENT YES-'   

                               .-SECURITY DB2----------.   
>--+------------------------+--+-----------------------+-------->
   '-PROGRAM TYPE--+-SUB--+-'  '-SECURITY--+-USER----+-'   
                   '-MAIN-'                '-DEFINER-'     

   .-STOP AFTER SYSTEM DEFAULT FAILURES-.   
>--+------------------------------------+----------------------->
   +-STOP AFTER--integer--FAILURES------+   
   '-CONTINUE AFTER FAILURE-------------'   

                                     .-COMMIT ON RETURN NO--.   
>--+------------------------------+--+----------------------+--->
   '-RUN OPTIONS--runtime-options-'  '-COMMIT ON RETURN YES-'   

   .-INHERIT SPECIAL REGISTERS-.  .-CALLED ON NULL INPUT-.   
>--+---------------------------+--+----------------------+------>
   '-DEFAULT SPECIAL REGISTERS-'                             

>--+---------------------+-------------------------------------><
   +-DISALLOW DEBUG MODE-+   
   +-ALLOW DEBUG MODE----+   
   '-DISABLE DEBUG MODE--'

Notes:

The same clause must not be specified more than one time.
The VARCHAR clause can only be specified is LANGUAGE C is specified.
With LANGUAGE JAVA, use a valid external-java-routine-name.


external-java-routine-name

|--+----------------+--| method-name |-------------------------->
   '-| jar-name |-:-'                    

>--+----------------------+-------------------------------------|
   '-| method-signature |-'   

jar-name

|--+--------------+--jar-id-------------------------------------|
   '-schema-name.-'           

method-name

   .--------------------------.                        
   V                          |                        
|----+----------------------+-+--class-id--+-.-----+------------>
     '-package-id-+-.-----+-'              |   (2) |   
                  |   (1) |                '-!-----'   
                  '-/-----'                            

>--method-id----------------------------------------------------|

method-signature

|--+---------------------------+--------------------------------|
   '-(-+-------------------+-)-'   
       | .-,-------------. |       
       | V               | |       
       '---java-datatype-+-'

Notes:

The slash (/) is supported for compatibility with previous releases of DB2® for z/OS®.
The exclamation point (!) is supported for compatibility with other products in the DB2 family.

Description

procedure-name

Names the stored procedure. The name cannot be a single asterisk, even if you specify it as a delimited identifier ("*"). The name, including the implicit or explicit qualifier, must not identify an existing stored procedure at the current server.

The schema name can be 'SYSIBM' or 'SYSPROC'. It can also be 'SYSTOOLS' if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.

(parameter-declaration,…)

Specifies the number of parameters of the stored procedure and the data type of each parameter, and optionally, the name of each parameter. A parameter for a stored procedure can be used only for input, only for output, or for both input and output. If an error is returned by the procedure, OUT parameters are undefined and INOUT parameters are unchanged.

All parameters are nullable except for numeric parameters in Java procedures, where numeric parameters, other than the DECIMAL types are not nullable in order to conform to the SQL/JRT standard.

IN

Identifies the parameter as an input parameter to the procedure. The value of the parameter on entry to the procedure is the value that is returned to the calling SQL application, even if changes are made to the parameter within the procedure.

IN is the default.

OUT

Identifies the parameter as an output parameter that is returned by the stored procedure.

INOUT

Identifies the parameter as both an input and output parameter for the stored procedure.

parameter-name

Names the parameter for use as an SQL variable. 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

The data type of the parameter is a built-in data type.

For more information on the data types, see built-in-type.

For parameters with a character or graphic data type, the PARAMETER CCSID clause or CCSID clause indicates the encoding scheme of the parameter. If you do not specify either of these clauses, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

distinct-type-name

The data type of the input parameter is a distinct type. Any length, precision, scale, subtype, or encoding scheme attributes for the parameter are those of the source type of the distinct type.

If you specify the name of the distinct type without a schema name, DB2 resolves the schema name by searching the schemas in the SQL path.

Although an input parameter with a character data type has an implicitly or explicitly specified subtype (BIT, SBCS, or MIXED), the value that is actually passed in the input argument on the CALL statement can have any subtype. Therefore, conversion of the input data to the subtype of the parameter might occur when the procedure is called. With ASCII or EBCDIC, an error occurs if mixed data that actually contains DBCS characters is used as the value for an input parameter that is declared with an SBCS subtype.

Parameters with a datetime data type or a distinct type are passed to the function as a different data type:

A datetime type parameter is passed as a character data type, and the data is passed in ISO format.
The encoding scheme for a datetime type parameter is the same as the implicitly or explicitly specified encoding scheme of any character or graphic string parameters. If no character or graphic string parameters are passed, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.
A distinct type parameter is passed as the source type of the distinct type.

AS LOCATOR

Specifies that a locator to the value of the parameter is passed to the procedure instead of the actual value. Specify AS LOCATOR only for parameters with a LOB data type or a distinct type based on a LOB data type. Passing locators instead of values can result in fewer bytes being passed to the procedure, especially when the value of the parameter is very large.

The AS LOCATOR clause has no effect on determining whether data types can be promoted.

TABLE LIKE table-name or view-name AS LOCATOR

Specifies that the parameter is a transition table. However, when the procedure is called, the actual values in the transition table are not passed to the stored procedure. A single value is passed instead. This single value is a locator to the table, which the procedure uses to access the columns of the transition table. A procedure with a table parameter can only be invoked from the triggered action of a trigger.

The use of TABLE LIKE provides an implicit definition of the transition table. It specifies that the transition table has the same number of columns as the identified table or view. If a table is specified, the transition table includes columns that are defined as implicitly hidden in the table. The columns have the same data type, length, precision, scale, subtype, and encoding scheme as the identified table or view, as they are described in catalog tables SYSCOLUMNS and SYSTABLESPACES. The number of columns and the attributes of those columns are determined at the time the CREATE PROCEDURE statement is processed. Any subsequent changes to the number of columns in the table or the attributes of those columns do not affect the parameters of the procedure.

table-name or view-name must identify a table or view that exists at the current server. The name must not identify a declared temporary table. The table that is identified can contain XML columns; however, the procedure cannot reference those XML columns. The name does not have to be the same name as the table that is associated with the transition table for the trigger. An unqualified table or view name is implicitly qualified according to the following rules:

If the CREATE PROCEDURE statement is embedded in a program, the implicit qualifier is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not used, the implicit qualifier is the owner of the plan or package.
If the CREATE PROCEDURE statement is dynamically prepared, the implicit qualifier is the SQL authorization ID in the CURRENT SCHEMA special register.

When the procedure is called, the corresponding columns of the transition table identified by the table locator and the table or view identified in the TABLE LIKE clause must have the same definition. The data type, length, precision, scale, and encoding scheme of these columns must match exactly. The description of the table or view at the time the CREATE PROCEDURE statement was executed is used.

Additionally, a character FOR BIT DATA column of the transition table cannot be passed as input for a table parameter for which the corresponding column of the table specified at the definition is not defined as character FOR BIT DATA. (The definition occurs with the CREATE PROCEDURE statement.) Likewise, a character column of the transition table that is not FOR BIT DATA cannot be passed as input for a table parameter for which the corresponding column of the table specified at the definition is defined as character FOR BIT DATA.

For more information about using table locators, see DB2 Application Programming and SQL Guide.

FENCED

Specifies that the procedure runs in an external address space.

DYNAMIC RESULT SETS integer

Specifies the maximum number of query result sets that the stored procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that there are no result sets. The value must be between 0 and 32767.

ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE

Specifies whether the procedure can be run in debugging mode. When DYNAMICRULES run behavior is in effect, the default is determined by using the value of the CURRENT DEBUG MODE special register. Otherwise the default is DISALLOW DEBUG MODE.

Do not specify this option unless LANGUAGE JAVA is in effect.

ALLOW DEBUG MODE: Specifies that the JAVA procedure can be run in debugging mode.
DISALLOW DEBUG MODE: Specifies that the JAVA procedure cannot be run in debugging mode.
You can use an ALTER PROCEDURE statement to change this option to ALLOW DEBUG MODE.
DISABLE DEBUG MODE: Specifies that the JAVA procedure can never be run in debugging mode.
The procedure cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE once the procedure has been created or altered using DISABLE DEBUG MODE. To change this option, you must drop and re-create the procedure using the option that you want.

PARAMETER CCSID or PARAMETER VARCHAR

Specifies the encoding scheme for string parameters, and in the case of LANGUAGE C, specifies the representation of variable length string parameters.

CCSID

Indicates whether the encoding scheme for character or graphic string parameters is ASCII, EBCDIC, or UNICODE. The default encoding scheme is the value specified in the CCSID clauses of the parameter list or in the field DEF ENCODING SCHEME on installation panel DSNTIPF.

This clause provides a convenient way to specify the encoding scheme for character or graphic string parameters. If individual CCSID clauses are specified for individual parameters in addition to this PARAMETER CCSID clause, the value specified in all of the CCSID clauses must be the same value that is specified in this clause.

This clause also specifies the encoding scheme to be used for system-generated parameters of the routine such as message tokens and DBINFO.

VARCHAR

Specifies that the representation of the values of varying length character string-parameters for procedures that specify LANGUAGE C.

This option can only be specified if LANGUAGE C is also specified.

NULTERM: Specifies that variable length character string parameters are represented in a NUL-terminated string form.
STRUCTURE: Specifies that variable length character string parameters are represented in a VARCHAR structure form.

Using the PARAMETER VARCHAR clause, there is no way to specify the VARCHAR form of an individual parameter as these is with PARAMETER CCSID. The PARAMETER VARCHAR clause only applies to parameters in the parameter list of a procedure and in the RETURNS clause. It does not apply to system-generated parameters of the routine such as message tokens and DBINFO.

In a data sharing environment, you should not specify the PARAMETER VARCHAR clause until all members of the data sharing group support the clause. If some group members support this clause and others do not, and PARAMETER VARCHAR is specified in an external routine, the routine will encounter different parameter forms depending on which group member invokes the routine.

EXTERNAL

Specifies that the CREATE PROCEDURE statement is being used to define a new procedure that is based on code written in an external programming language. If the NAME clause is not specified, 'NAME procedure-name' is assumed. The NAME clause is required for a LANGUAGE JAVA procedure because the default name is not valid for a Java procedure. In some cases, the default name will not be valid. To avoid invalid names, specify the NAME clause for the following types of procedures:

A procedure that is defined as LANGUAGE JAVA
A procedure that has a name that is greater than 8 bytes in length, contains an underscore, or does not conform to the rules for an ordinary identifier.

NAME string or identifier

Identifies the user-written code that implements the stored procedure.

If LANGUAGE is JAVA, string must be specified and enclosed in single quotation marks, with no extraneous blanks within the single quotation marks. It must specify a valid external-java-routine-name. If multiple strings are specified, the total length of all of them must not be greater than 1305 bytes and they must be separated by a space or a line break.

An external-java-routine-name contains the following parts:

jar-name

Identifies the name given to the JAR file when it was installed in the database. The name contains jar-id, which can optionally be qualified with a schema. Examples are "myJar" and "mySchema.myJar." The unqualified jar-id is implicitly qualified with a schema name according to the following rules:

If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the package or plan was created or last rebound. If the QUALIFIER was not specified, the schema name is the owner of the package or plan.
If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SCHEMA special register.

If jar-name is specified, it must exist when the CREATE PROCEDURE statement is processed. Do not specify a jar-name for a JAVA procedure for which NO SQL is also specified.

If jar-name is not specified, the procedure is loaded from the class file directly instead of being loaded from a JAR file. DB2 searches the directories in the CLASSPATH associated with the WLM Environment. Environmental variables for Java routines are specified in a data set identified in a JAVAENV DD card on the JCL used to start the address space for a WLM-managed stored procedure.

method-name

Identifies the name of the method and must not be longer than 254 bytes. Its package, class, and method ID's are specific to Java and as such are not limited to 18 bytes. In addition, the rules for what these can contain are not necessarily the same as the rules for an SQL ordinary identifier.

package-id: Identifies a package. The concatenated list of package-ids identifies the package that the class identifier is part of. If the class is part of a package, the method name must include the complete package prefix, such as "myPacks.StoredProcs." The Java virtual machine looks in the directory "/myPacks/StoredProcs/" for the classes.
class-id: Identifies the class identifier of the Java object.
method-id: Identifies the method identifier with the Java class to be invoked.

method-signature

Identifies a list of zero or more Java data types for the parameter list and must not be longer than 1024 bytes. Specify the method-signature if the procedure involves any input or output parameters that can be NULL. When the stored procedure being created is called, DB2 searches for a Java method with the exact method-signature. The number of java-datatype elements specified indicates how many parameters that the Java method must have.

A Java procedure can have no parameters. In this case, you code an empty set of parentheses for method-signature. If a Java method-signature is not specified, DB2 searches for a Java method with a signature derived from the default JDBC types associated with the SQL types specified in the parameter list of the CREATE PROCEDURE statement.

For other values of LANGUAGE, the value must conform to the naming conventions for MVS™ load modules: the value must be less than or equal to 8 bytes, and it must conform to the rules for an ordinary identifier with the exception that it must not contain an underscore.

LANGUAGE

This mandatory clause is used to specify the language interface convention to which the procedure body is written. All programs must be designed to run in the server's environment. Assembler, C, COBOL, and PL/I programs must be designed to run in IBM®'s Language Environment®.

ASSEMBLE: The stored procedure is written in Assembler.
C: The stored procedure is written in C or C++.
COBOL: The stored procedure is written in COBOL, including the OO-COBOL language extensions.
JAVA: The stored procedure is written in Java and is executed in the Java Virtual Machine. When LANGUAGE JAVA is specified, the EXTERNAL NAME clause must be specified with a valid external-java-routine-name and PARAMETER STYLE must be specified with JAVA. The procedure must be a public static method of the specified Java class.
Do not specify LANGUAGE JAVA when DBINFO, PROGRAM TYPE MAIN, or RUN OPTIONS is specified.
PLI: The stored procedure is written in PL/I.
REXX: The stored procedure is written in REXX. Do not specify LANGUAGE REXX when PARAMETER STYLE SQL is in effect. When REXX is specified, the procedure must use PARAMETER STYLE GENERAL or GENERAL WITH NULLS.

MODIFIES SQL DATA, READS SQL DATA, CONTAINS SQL, or NO SQL

Specifies the classification of SQL statements and nested routines that this routine can execute or invoke. The database manager verifies that the SQL statements issued by the procedure, and all routines locally invoked by the routine, are consistent with this specification; the verification is not performed when nested remote routines are invoked. For the classification of each statement, see SQL statement data access classification for routines.

MODIFIES SQL DATA: Specifies that the procedure can execute any SQL statement except statements that are not supported in procedures.
READS SQL DATA: Specifies that the procedure can execute statements with a data access indication of READS SQL DATA, CONTAINS SQL, or NO SQL. The procedure cannot execute SQL statements that modify data.
CONTAINS SQL: Specifies that the procedure can execute only SQL statements with an access indication of CONTAINS SQL or NO SQL. The procedure cannot execute statements that read or modify data.
NO SQL: Specifies that the procedure can execute only SQL statements with a data access classification of NO SQL. Do not specify NO SQL for a JAVA procedure that uses a JAR file.

PARAMETER STYLE

Identifies the linkage convention used to pass parameters to and return values from the stored procedure. All of the linkage conventions provide arguments to the stored procedure that contain the parameters specified on the CALL statement. Some of the linkage conventions pass additional arguments to the stored procedure that provide more information to the stored procedure. For more information on linkage conventions, see DB2 Application Programming and SQL Guide.

SQL

Specifies that, in addition to the parameters on the CALL statement, several additional parameters are passed to the stored procedure. The following parameters are passed:

The first n parameters that are specified on the CREATE PROCEDURE statement.
n parameters for indicator variables for the parameters.
The SQLSTATE to be returned.
The qualified name of the stored procedure.
The specific name of the stored procedure.
The SQL diagnostic string to be returned to DB2.
If DBINFO is specified, the DBINFO structure.

PARAMETER STYLE SQL is the default. Do not specify PARAMETER STYLE SQL when LANGUAGE REXX or LANGUAGE JAVA is in effect.

GENERAL

Specifies that the stored procedure uses a parameter passing mechanism where the stored procedure receives only the parameters specified on the CALL statement. Arguments to procedures defined with this parameter style cannot be null.

GENERAL WITH NULLS

Specifies that, in addition to the parameters on the CALL statement as specified in GENERAL, another argument is also passed to the stored procedure. The additional argument contains an indicator array with an element for each of the parameters on the CALL statement. In C, this is an array of short INTS. The indicator array enables the stored procedure to accept or return null parameter values.

JAVA

Specifies that the stored procedure uses a parameter passing convention that conforms to the Java and SQLJ Routines specifications. PARAMETER JAVA can be specified only if LANGUAGE is JAVA. JAVA must be specified for PARAMETER STYLE when LANGUAGE is JAVA.

INOUT and OUT parameters are passed as single-entry arrays. The INOUT and OUT parameters are declared in the Java method as single-element arrays of the Java type.

For REXX stored procedures (LANGUAGE REXX), GENERAL and GENERAL WITH NULLS are the only valid values for PARAMETER STYLE; therefore, specify one of these values and do not allow PARAMETER STYLE to default to SQL.

DETERMINISTIC or NOT DETERMINISTIC

Specifies whether the stored procedure returns the same results each time the stored procedure is called with the same IN and INOUT arguments.

DETERMINISTIC: The stored procedure always returns the same results each time the stored procedure is called with the same IN and INOUT arguments, if the referenced data in the database has not changed.
NOT DETERMINISTIC: The stored procedure might 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. NOT DETERMINISTIC is the default.

DB2 does not verify that the stored procedure code is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.

NO PACKAGE PATH or PACKAGE PATH package-path

Specifies the package path to use when the procedure is run. This is the list of the possible package collections into which the DBRM this is associated with the procedure is bound.

NO PACKAGE PATH

Specifies that the list of package collections for the procedure is the same as the list of package collection IDs for the calling program. If the calling program does not use a package, DB2 resolves the package by using the CURRENT PACKAGE PATH special register, the CURRENT PACKAGESET special register, or the PKLIST bind option (in this order). For information about how DB2 uses these three items, see DB2 Application Programming and SQL Guide.

PACKAGE PATH package-path

Specifies a list of package collections, in the same format as the SET CURRENT PACKAGE PATH special register.

If the COLLID clause is specified with PACKAGE PATH, the COLLID clause is ignored when the routine is invoked.

The package-path value that is provided when the procedure is created is checked when the CALL statement is prepared. If package-path contains SESSION_USER (or USER), PATH, or PACKAGE PATH, an error is returned when the package-path value is checked.

NO DBINFO or DBINFO

Specifies whether additional status information is passed to the stored procedure when it is invoked.

NO DBINFO: Additional information is not passed. NO DBINFO is the default.
DBINFO: An additional argument is passed when the stored procedure is invoked. 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 invoked the procedure. For details about the argument and its structure, see DB2 Application Programming and SQL Guide.
DBINFO can be specified only if PARAMETER STYLE SQL is specified.

NO COLLID or COLLID collection-id

Identifies the package collection that is to be used when the stored procedure is executed. This is the package collection into which the DBRM that is associated with the stored procedure is bound.

NO COLLID: The package collection for the stored procedure is the same as the package collection of the calling program. If the invoking program does not use a package, DB2 resolves the package by using the CURRENT PACKAGE PATH special register, the CURRENT PACKAGESET special register, or the PKLIST bind option (in this order). For details about how DB2 uses these three items, see the information on package resolution in DB2 Application Programming and SQL Guide.
NO COLLID is the default.
COLLID collection-id: The package collection for the stored procedure is the one specified.
For REXX stored procedures, collection-id can be DSNREXRR, DSNREXRS, DSNREXCR, or DSNREXCS.

WLM ENVIRONMENT

Identifies the WLM (workload manager) environment in which the stored procedure is to run when the DB2 stored procedure address space is WLM-established. The name of the WLM environment is an SQL identifier.

If you do not specify WLM ENVIRONMENT, the stored procedure runs in the default WLM-established stored procedure address space specified at installation time.

name: The WLM environment in which the stored procedure must run. If another stored procedure or a user-defined function calls the stored procedure and that calling routine is running in an address space that is not associated with the specified WLM environment, DB2 routes the stored procedure request to a different address space.
(name,*): When an SQL application program directly calls a stored procedure, the WLM environment in which the stored procedure runs.
If another stored procedure or a user-defined function calls the stored procedure, the stored procedure runs in the same WLM environment that the calling routine uses.

To define a stored procedure that is to run in a specified WLM environment, you must have appropriate authority for the WLM environment. For an example of a RACF® command that provides this authorization, see Running stored procedures.

ASUTIME

Specifies the total amount of processor time, in CPU service units, that a single invocation of a stored procedure can run. The value is unrelated to the ASUTIME column of the resource limit specification table. This option is ignored if LANGUAGE JAVA is specified.

When you are debugging a stored procedure, setting a limit can be helpful in case the stored procedure gets caught in a loop. For information on service units, see z/OS MVS Initialization and Tuning Guide.

NO LIMIT: There is no limit on the service units. NO LIMIT is the default.
LIMIT integer: The limit on the number of CPU service units is a positive integer in the range of 1 to 2 147 483 647. If the procedure uses more service units than the specified value, DB2 cancels the procedure. The CPU cycles that are consumed by parallel tasks in a procedure do not contribute towards the specified ASUTIME LIMIT.

STAY RESIDENT

Specifies whether the stored procedure load module is to remain resident in memory when the stored procedure ends. This option is ignored if LANGUAGE JAVA is specified.

NO: The load module is deleted from memory after the stored procedure ends. NO is the default.
YES: The load module remains resident in memory after the stored procedure ends.

PROGRAM TYPE

Specifies whether the stored procedure runs as a main routine or a subroutine.

SUB: The stored procedure runs as a subroutine. With LANGUAGE JAVA, PROGRAM TYPE SUB is the only valid option.
MAIN: The stored procedure runs as a main routine. With LANGUAGE REXX, PROGRAM TYPE MAIN is always in effect.

The default for PROGRAM TYPE is:

MAIN with LANGUAGE REXX
SUB with LANGUAGE JAVA
For other languages, the default depends on the value of the CURRENT RULES special register:
- MAIN when the value is DB2
- SUB when the value is STD

SECURITY

Specifies how the stored procedure interacts with an external security product, such as RACF, to control access to non-SQL resources.

DB2: The stored procedure does not require a special external security environment. If the stored procedure accesses resources that an external security product protects, the access is performed using the authorization ID associated with the stored procedure address space. DB2 is the default.
USER: An external security environment should be established for the stored procedure. If the stored procedure accesses resources that the external security product protects, the access is performed using the authorization ID of the user who invoked the stored procedure.
DEFINER: An external security environment should be established for the stored procedure. If the stored procedure accesses resources that the external security product protects, the access is performed using the authorization ID of the owner of the stored procedure.

STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER nn FAILURES, or CONTINUE AFTER FAILURE

Specifies whether the routine is to be put in a stopped state after some number of failures.

STOP AFTER SYSTEM DEFAULT FAILURES: Specifies that this routine should be placed in a stopped state after the number of failures indicated by the value of field MAX ABEND COUNT on installation panel DSNTIPX. This is the default.
STOP AFTER nn FAILURES: Specifies that this routine should be placed in a stopped state after nn failures. The value nn can be an integer from 1 to 32767.
CONTINUE AFTER FAILURE: Specifies that this routine should not be placed in a stopped state after any failure.

RUN OPTIONS runtime-options

Specifies the Language Environment run time options to be used for the stored procedure. For a REXX stored procedure, specifies the Language Environment run time options to be passed to the REXX language interface to DB2. You must specify runtime-options as a character string that is no longer than 254 bytes. If you do not specify RUN OPTIONS or pass an empty string, DB2 does not pass any run time options to Language Environment, and Language Environment uses its installation defaults.

Do not specify RUN OPTIONS when LANGUAGE JAVA is in effect.

For a description of the Language Environment run time options, see z/OS Language Environment Programming Reference.

COMMIT ON RETURN

Indicates whether DB2 commits the transaction immediately on return from the stored procedure.

NO

DB2 does not issue a commit when the stored procedure returns. NO is the default.

YES

DB2 issues a commit when the stored procedure returns if the following statements are true:

The SQLCODE that is returned by the CALL statement is not negative.
The stored procedure is not in a must abort state.

The commit operation includes the work that is performed by the calling application process and the stored procedure.

If the stored 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.

INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS

Specifies how special registers are set on entry to the routine. The default is INHERIT SPECIAL REGISTERS.

INHERIT SPECIAL REGISTERS: Specifies that the values of special registers are inherited according to the rules listed in the table for characteristics of special registers in a stored procedure in Special registers in a user-defined function or a stored procedure.
DEFAULT SPECIAL REGISTERS: Specifies that special registers are initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a stored procedure in Special registers in a user-defined function or a stored procedure.

CALLED ON NULL INPUT

Specifies that the procedure is to be called even if any or all argument values are null, which means that the procedure must be coded to test for null argument values. The procedure can return null or nonnull values. CALLED ON NULL INPUT is the default.

Notes

Owner privileges: The owner is authorized to call the procedure (EXECUTE privilege) and grant others the privilege to call the procedure. See GRANT (function or procedure privileges). For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.

Choosing data types for parameters: When you choose the data types of the parameters for your stored procedure, consider the rules of promotion that can affect the values of the parameters. (See Promotion of data types). For example, a constant that is one of the input arguments to the stored procedure might have a built-in data type that is different from the data type that the procedure expects, and more significantly, might not be promotable to that expected data type. Based on the rules of promotion, using the following data types for parameters is recommended:

INTEGER instead of SMALLINT
DOUBLE instead of REAL
VARCHAR instead of CHAR
VARGRAPHIC instead of GRAPHIC
VARBINARY instead of BINARY

For portability of functions across platforms that are not DB2 for z/OS, do not use the following data types, which might have different representations on different platforms:

FLOAT. Use DOUBLE or REAL instead.
NUMERIC. Use DECIMAL instead.

Specifying the encoding scheme for parameters: The encoding scheme of all of the parameters with a character or graphic string data type (both input and output parameters) must be the same—either all ASCII, all EBCDIC, or all UNICODE. If you specify the encoding scheme on the individual parameters, instead of using the PARAMETER CCSID to specify it for all parameters at once or allowing the encoding scheme to default to the system value, ensure that they all agree.

Character string representation considerations: The PARAMETER VARCHAR clause is specific to LANGUAGE C routines because of the native use of NUL-terminated strings in C. VARCHAR structure representation is useful when character string data is known to contain embedded NUL-terminators. It is also useful when it cannot be guaranteed that character string data does not contain embedded NUL-terminators.

PARAMETER VARCHAR does not apply to fixed length character strings, VARCHAR FOR BIT DATA, CLOB, DBCLOB, or implicitly generated parameters. The clause does not apply to VARCHAR FOR BIT DATA because BIT DATA can contain X'00' characters, and its value representation starts with length information. It does not apply to LOB data because a LOB value representation starts with length information.

PARAMETER VARCHAR does not apply to optional parameters that are implicitly provided to an external procedure. For example, a CREATE PROCEDURE statement for LANGUAGE C must also specify PARAMETER STYLE SQL, which returns an SQLSTATE NUL-terminated character string; that SQLSTATE will not be represented in VARCHAR structured form. Likewise, none of the parameters that represent the qualified name of the procedure, the specific name of the procedure, or the SQL diagnostic string that is returned to the database manager will be represented in VARCHAR structured form.

Running stored procedures: You can use the WLM ENVIRONMENT clause to identify the address space in which a stored procedure is to run. Using different WLM environments lets you isolate one group of programs from another. For example, you might choose to isolate programs based on security requirements and place all payroll applications in one WLM environment because those applications deal with sensitive data, such as employee salaries.

Regardless of where the stored procedure is to run, DB2 invokes RACF to determine whether you have appropriate authorization. You must have authorization to issue CREATE PROCEDURE statements that refer to the specified WLM environment or the DB2-established stored procedure address space. For example, the following RACF command authorizes DB2 user DB2USER1 to define stored procedures on DB2 subsystem DB2A that run in the WLM environment named PAYROLL.

  PERMIT  DB2A.WLMENV.PAYROLL  CLASS(DSNR) ID(DB2USER1)  ACCESS(READ)

Accessing result sets from nested stored procedures: When another stored procedure or a user-defined function calls a stored procedure, only the calling routine can access the result sets that the stored procedure returns. The result sets are not returned to the application that contains the outermost stored procedure or user-defined function in the sequence of nested calls.

When a stored procedure is nested, the result sets that are returned by the stored procedure are accessible only by the calling routine. The result sets are not returned to the application that contains the outermost stored procedure or user-defined function in the sequence of nested calls.

Restrictions for nested stored procedures: A stored procedure, user-defined function, or trigger cannot call a stored procedure that is defined with the COMMIT ON RETURN clause.

Alternative syntax and synonyms: To provide compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports the following keywords:

RESULT SET as a synonym for DYNAMIC RESULT SET
RESULT SETS as a synonym for DYNAMIC RESULT SETS
STANDARD CALL as a synonym for DB2SQL
SIMPLE CALL as a synonym for GENERAL
SIMPLE CALL WITH NULLS as a synonym for GENERAL WITH NULLS
VARIANT as a synonym for NOT DETERMINISTIC
NOT VARIANT as a synonym for DETERMINISTIC
NULL CALL as a synonym for CALLED ON NULL INPUT
PARAMETER STYLE DB2SQL as a synonym for PARAMETER STYLE SQL

Examples

Example 1: Create the definition for a stored procedure that is written in COBOL. The procedure accepts an assembly part number and returns the number of parts that make up the assembly, the total part cost, and a result set. The result set lists the part numbers, quantity, and unit cost of each part. Assume that the input parameter cannot contain a null value and that the procedure is to run in a WLM environment called PARTSA.

  CREATE PROCEDURE SYSPROC.MYPROC(IN INT, OUT INT, OUT DECIMAL(7,2))
         LANGUAGE COBOL
         EXTERNAL NAME MYMODULE
         PARAMETER STYLE GENERAL
         WLM ENVIRONMENT PARTSA
         DYNAMIC RESULT SETS 1;

Example 2: Create the definition for the stored procedure described in Example 1, except use the linkage convention that passes more information than the parameter specified on the CALL statement. Specify Language Environment run time options HEAP, BELOW, ALL31, and STACK.

  CREATE PROCEDURE SYSPROC.MYPROC(IN INT, OUT INT, OUT DECIMAL(7,2))
         LANGUAGE COBOL
         EXTERNAL NAME MYMODULE
         PARAMETER STYLE SQL
         WLM ENVIRONMENT PARTSA
         DYNAMIC RESULT SETS 1
         RUN OPTIONS 'HEAP(,,ANY),BELOW(4K,,),ALL31(ON),STACK(,,ANY,)';

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

  CREATE PROCEDURE PARTS_ON_HAND(IN PARTNUM INT, 
                                 OUT COST DECIMAL(7,2), 
                                 OUT QUANTITY INT)
         LANGUAGE JAVA
         EXTERNAL NAME 'PARTS.ONHAND'
         PARAMETER STYLE JAVA;