ALTER PROCEDURE (external)

The ALTER PROCEDURE statement changes the description of 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 implicitly or explicitly specified.

Authorization

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

Ownership of the stored procedure
The ALTERIN privilege on the schema
SYSADM or SYSCTRL authority
System DBADM

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

Start of change If the authorization ID that is used to alter the procedure has installation SYSADM authority, the procedure is identified as system-defined procedure when the procedure definition is reevaluated. 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 package.

If the statement is dynamically prepared, the privilege set is the set of privileges that are held by the SQL authorization IDs of the process. The specified routine name can include a schema name (a qualifier). However, if the schema name is not the same as one of these SQL authorization IDs, one of the following conditions must be met:

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

If the environment in which the stored procedure is to run is being changed, the authorization ID must have authority to use the WLM environment. This authorization is obtained from an external security product, such as RACF®.

Syntax


>>-ALTER PROCEDURE--procedure-name--| option-list |------------><

option-list: (Specify options in any order. Specify at least one option. Do not specify the same option more than once.)


>>-DYNAMIC RESULT SETS--integer--------------------------------->

                                          (1)     
>--EXTERNAL NAME--+-external-program-name-----+----------------->
                  '-identifier----------------'   

>--LANGUAGE--+-ASSEMBLE-+--------------------------------------->
             +-C--------+   
             +-COBOL----+   
             +-JAVA-----+   
             +-PLI------+   
             '-REXX-----'   

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

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

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

>--+-NO COLLID-------------+------------------------------------>
   '-COLLID--collection-id-'   

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

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

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

>--RUN OPTIONS--run-time-options--COMMIT ON RETURN--+-NO--+----->
                                                    '-YES-'   

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

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

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

Notes:

If LANGUAGE is JAVA, EXTERNAL NAME must be specified with 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

Identifies the stored procedure to be altered.

DYNAMIC RESULT SETS integer

Specifies the maximum number of query result sets that the stored procedure can return. The value must be between 0 and 32767.

EXTERNAL NAME external-program-name or identifier

Specifies the name of the MVS™ load module for the program that runs when the procedure name is specified in an SQL CALL statement.

If LANGUAGE is JAVA, external-program-name 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 external-program-name values are specified, the total length of all of the values must not be greater than 1305 bytes and each value must be separated by a space or a line break. Do not specify a JAR file for a Java procedure for which NO SQL is in effect.

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 ALTER PROCEDURE statement is processed.

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 ALTER 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

Specifies the application programming language in which the stored procedure is written. 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 also 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 in effect.
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 specified.

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.

Do not specify PARAMETER STYLE SQL when LANGUAGE REXX is specified.

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 integers. 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 STYLE JAVA can be specified only if LANGUAGE is JAVA. If the ALTER PROCEDURE statement results in changing LANGUAGE to JAVA, PARAMETER STYLE JAVA, and an EXTERNAL NAME clause might need to be specified to provide appropriate values. 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.

PARAMETER STYLE SQL cannot be used with LANGUAGE REXX.

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.

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

Identifies 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 collections 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 used in the 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 associated with the procedure definition is checked when the procedure is invoked. If package-path contains SESSION_USER, USER, PATH, or PACKAGE PATH, an error is returned when the package-path value is checked.

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

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.
DBINFO: An additional argument is passed when the stored procedure is invoked. The argument is a structure that contains information such as the application run time authorization ID, the schema name, the name of a table or column that the procedure might be inserting into or updating, and identification of the database server 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: Specifies that 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.
COLLID collection-id: Identifies the package collection that is to be used when the stored procedure is executed. It is the name of the package collection into which the DBRM associated with the stored procedure is bound.
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.

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 the stored procedure is called directly by an SQL application program, 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 change the environment in which the procedure is to run, 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 in the resource limit specification table.

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 CPU service units, see z/OS MVS Initialization and Tuning Guide.

NO LIMIT: There is no limit on the service units.
LIMIT integer: The limit on the service units is a positive integer in the range of 1 to 2 147 483 647. If the stored procedure uses more service units than the specified value, DB2 cancels the stored procedure.

STAY RESIDENT

Specifies whether the stored procedure load module is to remain resident in memory when the stored procedure ends.

NO: The load module is deleted from memory after the stored procedure ends. Use NO for non-reentrant stored procedures.
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. If PROGRAM TYPE is altered, the stored procedure needs to be re-compiled for the change to take effect.

SUB: The stored procedure runs as a subroutine.
Do not specify PROGRAM TYPE SUB for stored procedures with a LANGUAGE value of REXX.
MAIN: The stored procedure runs as a main routine.
Do not specify PROGRAM TYPE MAIN when LANGUAGE JAVA is specified.

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

RUN OPTIONS run-time-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 run-time-options as a character string that is no longer than 254 bytes. To replace any existing run time options with no options, specify an empty string with RUN OPTIONS. When you specify an empty string, DB2 does not pass any run time options to Language Environment, and Language Environment uses its installation defaults. For a description of the Language Environment run time options, see z/OS Language Environment Programming Reference.

Do not specify RUN OPTIONS when LANGUAGE JAVA is specified.

COMMIT ON RETURN

Indicates whether DB2 is to commit the transaction immediately on return from the stored procedure.

NO

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

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

INHERIT SPECIAL REGISTERS: Indicates that 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: Indicates 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 of the 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.

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 field DSNTIPX.
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.

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

Specifies whether the procedure can be run in debugging mode.

Do not specify this option unless the procedure is defined with LANGUAGE JAVA.

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

Notes

Invalidation of packages: When an external procedure is altered, all the packages that refer to that procedure are marked invalid.

LANGUAGE C and the PARAMETER VARCHAR clause: The ALTER PROCEDURE statement does not allow you to alter the value of the PARAMETER VARCHAR or PARAMETER CCSID clauses that are associated with the procedure definition. However, you can alter the LANGUAGE clause for the procedure. If the PARAMETER VARCHAR clause is specified for the creation of a LANGUAGE C procedure, the catalog information for that option is not affected by subsequent ALTER PROCEDURE statements. The procedure might be changed to a language other than C, in which case the PARAMETER VARCHAR setting is ignored. If the procedure is later changed back to LANGUAGE C, the setting of the PARAMETER VARCHAR option that was specified for the CREATE PROCEDURE statement (which is still in the catalog) will be used.

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

DYNAMIC RESULT SET, RESULT SET, and RESULT SETS as synonyms 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

Assume that stored procedure SYSPROC.MYPROC is currently defined to run in WLM environment PARTSA and that you have appropriate authority on that WLM environment and WLM environment PARTSEC. Change the definition of the stored procedure so that it runs in PARTSEC.

    ALTER PROCEDURE SYSPROC.MYPROC WLM ENVIRONMENT PARTSEC;