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 in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization

Start of changeTo create a new procedure in the implicit or explicit schema, the privilege set that is defined below must include at least one of the following:End of change

  • The CREATEIN privilege on the schema
  • SYSADM or SYSCTRL authority
  • System DBADM
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

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

FL 507To replace a procedure, the privilege set that is defined below must include at least one of the following:
  • Ownership of the procedure
  • Both the DROPIN and CREATEIN privileges on the schema
  • System DBADM authority
  • SYSCTRL authority
  • SYSADM authority
  • • Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

Start of changeIf the authorization ID that is used to create the procedure has the installation SYSADM authority or the installation SYSOPR authority and if the current SQLID is set to SYSINSTL, 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. For more information, see Managing authorizations for creation of stored procedures in WLM environments.

Also, if the stored procedure uses a user-defined type as a parameter, this authorization ID must have the USAGE privilege on each parameter that is defined as a user-defined type.

Syntax

Read syntax diagramSkip visual syntax diagramCREATEOR REPLACEPROCEDUREprocedure-name (,parameter-declaration)option-list

parameter-declaration:

Read syntax diagramSkip visual syntax diagramINOUTINOUT1parameter-nameparameter-type
Notes:
  • 1 For a REXX stored procedure, only one parameter can have type OUT or INOUT. That parameter must be declared last.

parameter-type:

Read syntax diagramSkip visual syntax diagramdata-typeAS LOCATOR1TABLE LIKEtable-nameview-nameAS LOCATOR
Notes:
  • 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.

data-type:

Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-name

built-in-type:

Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERIC(5,0)( integer, integer)FLOAT(53)( integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)( integer)CHARACTERCHARVARYINGVARCHAR( integer)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDBITDATACHARACTERCHARLARGE OBJECTCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEFORSBCSMIXEDDATAGRAPHIC(1)( integer)VARGRAPHIC(integer)DBCLOB(1M)( integerKMG)CCSIDASCIIEBCDICUNICODEBINARY(1)( integer)BINARY VARYINGVARBINARY( integer)BINARY LARGE OBJECTBLOB(1M)( integerKMG)DATETIMETIMESTAMP(6)(integer)WITHOUT TIME ZONEWITH TIME ZONEROWID

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

Read syntax diagramSkip visual syntax diagramSPECIFICprocedure-nameDYNAMIC RESULT SETS 0DYNAMIC RESULT SETSintegerPARAMETER1CCSIDASCIIEBCDICUNICODEVARCHAR2NULTERMSTRUCTUREEXTERNAL NAMEstring3identifier LANGUAGE ASSEMBLECCOBOLJAVAPLIREXX MODIFIES SQL DATAREADS SQL DATACONTAINS SQLNO SQLPARAMETER STYLE SQLPARAMETER STYLEGENERALGENERAL WITH NULLSJAVANOT DETERMINISTICDETERMINISTICNO PACKAGE PATHPACKAGE PATHpackage-pathFENCEDNO DBINFODBINFONO COLLIDCOLLIDcollection-idWLM ENVIRONMENTname(name,*)ASUTIME NO LIMITASUTIMELIMITintegerSTAY RESIDENT NOSTAY RESIDENT YESPROGRAM TYPESUBMAINSECURITY DB2SECURITYUSERDEFINERSTOP AFTER SYSTEM DEFAULT FAILURESSTOP AFTERintegerFAILURESCONTINUE AFTER FAILURERUN OPTIONSruntime-optionsCOMMIT ON RETURN NOCOMMIT ON RETURN YES INHERIT SPECIAL REGISTERSDEFAULT SPECIAL REGISTERS CALLED ON NULL INPUT DISALLOW DEBUG MODEALLOW DEBUG MODEDISABLE DEBUG MODE
Notes:
  • 1 The same clause must not be specified more than one time.
  • 2 The VARCHAR clause can only be specified is LANGUAGE C is specified.
  • 3 With LANGUAGE JAVA, use a valid external-java-routine-name.
Read syntax diagramSkip visual syntax diagram
external-java-routine-name
Read syntax diagramSkip visual syntax diagramjar-name:method-namemethod-signature
jar-name
Read syntax diagramSkip visual syntax diagramschema-name.jar-id
method-name
Read syntax diagramSkip visual syntax diagram package-id./1class-id.!2method-id
method-signature
Read syntax diagramSkip visual syntax diagram(, java-datatype)
Notes:
  • 1 The slash (/) is supported for compatibility with previous releases of Db2 for z/OS®.
  • 2 The exclamation point (!) is supported for compatibility with other products in the Db2 family.

Description

Start of changeOR REPLACEEnd of change
Start of changeFL 507Specifies that if the procedure exists at the current server, the existing definition is replaced. This option is ignored if a definition for the procedure does not exist at the current server. If a procedure exists with the specified name, the existing procedure must be an external procedure.
If the procedure exists, the procedure is replaced. The existing definition is dropped before the new definition is replaced in the catalog, with the exception that privileges that were granted on the procedure are not affected. The procedure is recreated as follows:
  • Any existing comment is discarded
  • The definition of the procedure can change
  • The timestamp that is associated with the procedure definition is updated
  • The owner of the procedure can change
  • System default values are used for options that are not explicitly specified, even if those options were explicitly specified when the procedure that is being replaced was originally defined
To replace an existing procedure, one of the following conditions must be meet:
  • the SPECIFIC clause must be specified with the procedure name
  • the signature of the new procedure definition must match the signature of the existing procedure definition, except for parameter names
End of change
procedure-name

Start of changeNames the stored procedure. The name, including the implicit or explicit qualifier, must not identify an existing stored procedure at the current server unless the OR REPLACE clause is specified.End of change

The name cannot be a single asterisk, even if you specify it as a delimited identifier ("*").

Start of changeThe schema name can be 'SYSTOOLS' or 'SYSFUN' if the privilege set includes the SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM', 'SYSIBMADM', or 'SYSPROC'.End of change

(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 user-defined type.

If you specify the name of a user-defined type without a schema name, Db2 resolves the user-defined type by searching the schemas in the SQL path.

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.

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.

Start of changeSPECIFIC procedure-nameEnd of change
Start of changeFL 507Specifies the procedure name as the specific name for the procedure. The name must be the same as the procedure name.

If you do not specify a schema name, the schema name is the same as the explicit or implicit schema name of the procedure name (procedure-name). If you specify a schema name, it must be the same as the explicit or implicit schema name of the procedure name.

If you do not specify the SPECIFIC clause, the specific name is the name of the procedure.

Specify the SPECIFIC clause when replacing an existing procedure in the following situations.

  • The parameter list of the existing procedure includes a table parameter
  • The CREATE statement specifies changes to the parameter list other than parameter names
End of change
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.

You must have appropriate authority for the WLM environment.

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 1 - 2147483647. 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

Considerations for all types of procedures
For considerations that apply to all types of procedures, see CREATE PROCEDURE.
Character string representation considerations:

The PARAMETER VARCHAR clause is specific to external procedures 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.

Environment for 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)
Alternative syntax and synonyms:
To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following alternative syntax:
  • 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;