CREATE PROCEDURE (external)
The CREATE PROCEDURE statement defines an external stored procedure at the current server.
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.
To 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:
- The CREATEIN privilege on the schema
- SYSADM or SYSCTRL authority
- System DBADM
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.
- 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)
If 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.
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.
- 1 For a REXX stored procedure, only one parameter can have type OUT or INOUT. That parameter must be declared last.
- 1 AS LOCATOR can be specified only for a LOB data type or a distinct type based on a LOB data type.
option-list: (The options can be specified in any order.)
- 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.
- OR REPLACE
- FL 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:
To replace an existing procedure, one of the following conditions must be meet:
- 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
- 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
Names 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.
The name cannot be a single asterisk, even if you specify it as a delimited identifier ("*").
The 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'.
- 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.
- 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.
- Identifies the parameter as an output parameter that is returned by the stored procedure.
- Identifies the parameter as both an input and output parameter for the stored procedure.
- Names the parameter for use as an SQL variable. The name cannot be the same as any other parameter-name for the procedure.
- 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.
- 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.
- 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 Accessing transition tables in a user-defined function or stored procedure.
- SPECIFIC procedure-name
- FL 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
- 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
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
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.
- 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
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.
- 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.
- Specifies that variable length character string parameters are represented in a NUL-terminated string form.
- 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.
- 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
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:
- 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
- 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.
- 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.
- 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.
- Identifies the class identifier of the Java object.
- Identifies the method identifier with the Java class to be invoked.
- 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
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.
- 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®.
- The stored procedure is written in Assembler.
- The stored procedure is written in C or C++.
- The stored procedure is written in COBOL, including the OO-COBOL language extensions.
- 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.
- The stored procedure is written in PL/I.
- 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 Linkage conventions for external stored procedures.
- 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.
- 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.
- 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.
- DETERMINISTIC or NOT DETERMINISTIC
whether the stored procedure returns the same results each time the
stored procedure is called with the same IN and INOUT arguments.
- 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 Binding an application plan.
- 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.
- 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 DBINFO structure.
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 Binding an application plan.
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.
- 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.
- 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.
- 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
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.
- The load module is deleted from memory after the stored procedure ends. NO is the default.
- 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.
- The stored procedure runs as a subroutine. With LANGUAGE JAVA, PROGRAM TYPE SUB is the only valid option.
- 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
- Specifies how the stored procedure
interacts with an external security product, such as RACF®, to control access to non-SQL resources.
- 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.
- 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.
- 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
whether the routine is to be put in a stopped state after some number
- 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 Language Environment Programming Reference .
- COMMIT ON RETURN
- Indicates whether Db2 commits the transaction immediately
on return from the stored procedure.
- Db2 does not issue a commit when the stored procedure returns. NO is the default.
- 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.
- 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
- 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;