ALTER PROCEDURE statement (SQL - external procedure) (deprecated)
The ALTER PROCEDURE statement changes the description, at the current server, of an external SQL procedure.
Invocation for ALTER PROCEDURE (SQL - external)
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 for ALTER PROCEDURE (SQL - external)
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
- 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 ALTERIN privilege on the schema.
If the authorization ID that is used to alter 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 the procedure definition is reevaluated.
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.
- 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®. For more information, see Managing authorizations for creation of stored procedures in WLM environments.
Syntax for ALTER PROCEDURE (SQL - external)
option-list: (Specify options in any order. Specify at least one option. Do not specify the same option more than once.)
Description for ALTER PROCEDURE (SQL - external)
- 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 in the range 0–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 Binding an application plan.
- 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.
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 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 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 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 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 1–32767.
- CONTINUE AFTER FAILURES
- Specifies that this routine should not be placed in a stopped state after any failure.
Notes for ALTER PROCEDURE (SQL - external)
- 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.
For more information, see Migrating an external SQL procedure to a native SQL procedure.
- 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 for ALTER PROCEDURE (SQL - external)
ALTER PROCEDURE UPDATE_SALARY_1
COMMIT ON RETURN YES
WLM ENVIRONMENT WLMSQLP;