This statement can be embedded in an application program or issued interactively.
To execute the ALTER PROCEDURE command, users must possess either overall SYSADM or SYSCTRL authority for the location, or be the owner of the stored procedure affected.
Bold text indicates clauses or options that are supported only syntactically.
ALTER PROCEDURE procedure-name
[DYNAMIC RESULT {SET | SETS} integer]
[EXTERNAL NAME {'string' | indentifier}]
[LANGUAGE {ASSEMBLE | C | COBOL | COMPJAVA | PLI | REXX}]
[PARAMETER STYLE {DB2SQL | GENERAL | GENERAL WITH NULLS | JAVA}]
[{DETERMINISTIC | NOT DETERMINISTIC}]
[{CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL}]
[{DBINFO | NODBINFO}]
[{NO COLLID | COLLID collection-id}]
[{WLM ENVIRONMENT {name | (name,*)}} | NO WLM ENVIRONMENT}]
[ASUTIME {NO LIMIT | LIMIT integer}]
[STAY RESIDENT {NO | YES}]
[PROGRAM TYPE {SUB | MAIN}]
[SECURITY {DB2 | USER | DEFINER}]
[RUN OPTIONS run-time-options]
[COMMIT ON RETURN {NO | YES}]
[CALLED ON NULL INPUT]
[{STOP AFTER SYSTEM DEFAULT FAILURES |
STOP AFTER integer FAILURES |
CONTINUE AFTER FAILURE}]
Identifies the stored procedure to be altered. The name is implicitly or explicitly qualified by a schema name. If the name is not explicitly qualified, it is implicitly qualified with a schema name according to the following rules:
Specifies the maximum number of query result sets that the stored procedure can return. The value must be between 0 and 32767.
Identifies the program that runs when the procedure name is specified in a CALL statement.
The name can be a string constant that is no longer than 8 characters or a short identifier.
The program does not need to exist when the ALTER PROCEDURE statement is executed. However, it must exist and be accessible by the current server when a CALL statement for the stored procedure is issued.
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. (Not currently available with XDB Server.) |
| C | The stored procedure is written in C or C++. (Not currently available with XDB Server.) |
| COBOL | The stored procedure is written in COBOL, including the OO-COBOL language extensions. |
| COMPJAVA | The stored procedure is written in Java and is a compiled program. (Not currently available with XDB Server.) |
| PL/I | The stored procedure is written in PL/I. (Not currently available with XDB Server.) |
| REXX | The stored procedure is written in REXX. Do not specify LANGUAGE REXX when PARAMETER STYLE DB2SQL or NO WLM ENVIRONMENT is in effect. (Not currently available with XDB Server.) |
Identifies the linkage convention used to pass parameters to 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, refer to appropriate DB2 documentation for a full explanation.
| DB2SQL | In addition to the parameters on the CALL statement, the following arguments are also passed to the stored procedure:
|
If DBINFO is specified, an additional parameter, the DB2INFO structure, might also be passed. Do not specify DB2 SQL when LANGUAGE REXX is in effect.
| GENERAL | Only the parameters on the CALL statement are passed to the stored procedure. The parameters cannot be null. |
| GENERAL WITH NULLS | In addition to the parameters on the CALL statement, another argument is also passed to the stored procedure. The additional argument contains a vector of null indicators for each of the parameters on the CALL statement that enables the stored procedure to accept or return null parameter values. |
| JAVA | The stored procedure uses a convention for passing parameters that conforms to the Java and SQLJ specifications. INOUT and
OUT parameters are passed as single-entry arrays. The DBINFO structure is not passed.
JAVA can be specified only if LANGUAGE is COMPJAVA. For REXX stored procedures (LANGUAGE REXX), GENERAL and GENERAL WITH NULLS are the only valid values for PARAMETER STYLE. For LANGUAGE COMPJAVA, JAVA is the only valid value. |
Specifies whether the stored procedure returns the same result from successive calls with identical input arguments.
| NOT DETERMINISTIC | The stored procedure might not return the same result from successive calls with identical input arguments. |
| DETERMINISTIC | The stored procedure returns the same result from successive calls with identical input arguments. XDB does not verify that the stored procedure code is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC. |
Indicates whether the stored procedure issues any SQL statements and, if so, what type.
| NO SQL | The stored procedure cannot execute any SQL statements. |
| MODIFIES SQL DATA | The stored procedure can execute any SQL statement except those statements that are not supported in any stored procedure. |
| READS SQL DATA | The stored procedure cannot execute SQL statements that modify data. SQL statements that are not supported in any stored procedure return a different error. |
| CONTAINS SQL | The stored procedure cannot execute any SQL statements that read or modify data. SQL statements that are not supported in any stored procedure return a different error. |
Specifies whether specific information known by XDB 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. |
Identifies the package collection that is used when the stored procedure is executed.
| NO COLLID | The package collection for the stored procedure is the same as the package collection of the program that invokes the stored procedure. If a trigger invokes the stored procedure, the collection of the trigger package is used. If the invoking program does not use a package, XDB resolves the package by using the CURRENT PACKAGE PATH special register or the CURRENT PACKAGESET special register. |
| COLLID collection-id | The name of the package collection that is used when the function is executed. |
This clause has no functional equivalent in the XDB Server operating environment.
Indicates that the stored procedure is to run in the XDB-established stored procedure address space.
Do not specify NO WLM ENVIRONMENT if the definition of the stored procedure implicitly or explicitly includes the following clauses or parameters:
To change the procedure to run in the XDB-established stored procedure address space, you must have appropriate authority for the XDB-established stored procedure address space.
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.
| 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 2GB. If the stored procedure uses more service units than the specified value, the stored procedure is canceled. |
The XDB Server operating environment only provides NO LIMIT at this time.
Specifies whether the stored procedure load module remains 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. |
The XDB Server provides an operating environment where the function does not remain resident.
Specifies whether the stored procedure runs as a main routine or a subroutine.
| SUB | The stored procedure runs as a subroutine. |
| MAIN | The stored procedure runs as a main routine. |
This clause has no functional equivalent in the XDB Server operating environment.
This clause has no functional equivalent in the XDB Server operating environment.
Indicates whether XDB commits the transaction immediately on return from the stored procedure.
| NO | XDB does not issue a commit when the stored procedure returns. |
| YES | XDB issues a commit when the stored procedure returns if the following statements are true:
|
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.
Specifices that the stored procedure will be called even if any of the input arguments is null, making the procedure responsible for testing for null argument values. The result is the null value.
Changes are immediate: Any changes that the ALTER PROCEDURE statement cause to the definition of a procedure take effect immediately.
The changed definition is used the next time that the procedure is called.
Restrictions for nested stored procedures: A stored procedure, user-defined function, or trigger cannot call a stored procedure that is defined with the COMMIT ON RETURN clause.
Specifies whether the routine is to be put in a stopped state after some number of failures.
This clause is only implemented syntactically in the XDB Server operating environment.
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;