The specified procedure is altered. The owner of the procedure and all privileges on the procedure are preserved.
If procedure-name() is specified, the procedure identified must have zero parameters.
If an unqualified distinct type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type.
For data types that have a length, precision, or scale attribute, use one of the following:
Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.
The result sets are scrollable if a cursor is used to return a result set and the cursor is scrollable. If a cursor is used to return a result set, the result set starts with the current position. Thus, if 5 FETCH NEXT operations have been performed prior to returning from the procedure, the result set will start with the 6th row of the result set.
Result sets are only returned if both the following are true:
For more information about result sets see SET RESULT SETS.
If DEBUG MODE is not specified, but a DBGVIEW option in the SET OPTION statement is specified, the procedure cannot be debugged by the Unified Debugger, but can be debugged by the system debug facilities. If neither DEBUG MODE nor a DBGVIEW option is specified, the debug mode used is from the CURRENT DEBUG MODE special register.
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 must have been defined as WITH HOLD to be usable after the commit.
The maximum number of parameters allowed in an SQL procedure is 1024.
SET OPTION DBGVIEW = *SOURCEFor more information, see SET OPTION.
The options CLOSQLCSR, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the ALTER PROCEDURE statement.
CONNECT, SET CONNECTION, RELEASE, DISCONNECT, and SET TRANSACTION statements are not allowed in a procedure that is running on a remote application server. COMMIT and ROLLBACK statements are not allowed in an ATOMIC SQL procedure or in a procedure that is running on a connection to a remote application server.
ALTER PROCEDURE (SQL) with a REPLACE keyword is not allowed in an SQL-routine-body.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.