ALTER PROCEDURE (SQL - external)

The ALTER PROCEDURE statement changes the description, at the current server, of an external SQL procedure.

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

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

The SQL authorization ID that is used to alter the procedure definition must have appropriate authority for the WLM environment in which the procedure is currently defined to run. 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--------------------------------->

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

>--+-NOT DETERMINISTIC-+--+-MODIFIES SQL DATA-+----------------->
   '-DETERMINISTIC-----'  +-READS SQL DATA----+   
                          '-CONTAINS 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-'   

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

>--+-INHERIT SPECIAL REGISTERS-+-------------------------------->
   '-DEFAULT SPECIAL REGISTERS-'   

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

Description

procedure-name

Identifies the stored procedure to be altered.

DYNAMIC RESULT SETS integer

Specifies the maximum number of query result sets that the 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. 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.

NOT DETERMINISTIC or DETERMINISTIC

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

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

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

MODIFIES SQL DATA, READS SQL DATA, or CONTAINS 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. Statements that are not supported in any procedure will return an error.

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 or CONTAINS 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 COLLID or COLLID collection-id

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

NO COLLID: Indicates that the package collection for the 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: Specifies the package collection for the procedure.

WLM ENVIRONMENT name or (name,*)

Identifies the WLM (workload manager) environment in which the 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: Specifies the WLM environment in which the procedure must run. If another routine calls the procedure and that calling routine is running in an address space that is not associated with the specified WLM environment, DB2 routes the procedure request to a different address space.
(name,*): When an SQL application program directly calls a procedure, name specifies the WLM environment in which the stored procedure runs.
If another routine calls the procedure, the 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 procedure can run. The value is unrelated to the ASUTIME column of the resource limit specification table.

When you are debugging a procedure, setting a limit can be helpful in case the 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 number of CPU service units that the procedure can run.
LIMIT integer: The limit on the number of CPU service units is a positive integer in the range of 1 to 2 147 483 647. If the procedure uses more service units than the specified value, DB2 cancels the procedure. The CPU cycles that are consumed by parallel tasks in a procedure do not contribute towards the specified ASUTIME LIMIT.

STAY RESIDENT

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

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

PROGRAM TYPE

Specifies whether the 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 procedure runs as a subroutine.
MAIN: The procedure runs as a main routine.

SECURITY

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

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

RUN OPTIONS run-time-options

Specifies the Language Environment® run time options that are to be used for the procedure. You must specify run-time-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.

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

NO

DB2 does not issue a commit when the procedure returns.

YES

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

A positive SQLCODE is returned by the CALL statement.
The procedure is not in a must abort state.

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

If the procedure returns result sets, the cursors that are associated with the result sets must have been defined as WITH HOLD to be usable after the commit.

INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS

Specifies how special registers are set on entry to the routine.

INHERIT SPECIAL REGISTERS: Specifies that special registers should be inherited according to the rules listed in the table for characteristics of special registers in a procedure in Table 1.
DEFAULT SPECIAL REGISTERS: Specifies that special registers should be initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a procedure in Table 1.

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

Specifies if the routine is stopped after 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.
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 FAILURES: Specifies that this routine should not be placed in a stopped state after any failure.

Notes

Changing to a native SQL procedure: You cannot change an external SQL procedure to a native SQL procedure. You can drop the procedure that you want to change using the DROP statement and create a native SQL procedure with a similar definition using the CREATE PROCEDURE statement. Alternatively, you can create a native SQL procedure using a different schema.

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

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

RESULT SET, RESULT SETS, and DYNAMIC RESULT SET as synonyms for DYNAMIC RESULT SETS.
VARIANT as a synonym for NOT DETERMINISTIC
NOT VARIANT as a synonym for DETERMINISTIC

Example

Modify the definition for an SQL procedure so that SQL changes are committed on return from the SQL procedure and the SQL procedure runs in the WLM environment named WLMSQLP.

ALTER PROCEDURE UPDATE_SALARY_1
 COMMIT ON RETURN YES
 WLM ENVIRONMENT WLMSQLP;