The ALTER PROCEDURE (SQL) statement alters a procedure at the current server.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
If a distinct type is referenced in a parameter-declaration, the privileges held by the authorization ID of the statement must include at least one of the following:
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure and Corresponding System Authorities When Checking Privileges to a Distinct Type.
>>-ALTER--------------------------------------------------------> >--+-PROCEDURE--procedure-name--+------------------------------+-+--> | '-(--+--------------------+--)-' | | | .-,--------------. | | | | V | | | | '---parameter-type-+-' | '-SPECIFIC PROCEDURE--specific-name---------------------------' .-ALTER-. >--+-+-------+--option-list---------+-------------------------->< '-REPLACE--routine-specification-' routine-specification |--+-------------------------------------+--+-------------+-----> '-(--+---------------------------+--)-' '-option-list-' | .-,---------------------. | | V | | '---parameter-declaration-+-' >--+----------------------+--SQL-routine-body-------------------| '-SET OPTION-statement-' parameter-declaration .-IN----. |--+-------+--parameter-name--data-type-------------------------| +-OUT---+ '-INOUT-' parameter-type |----data-type--------------------------------------------------| data-type |--+-built-in-type------+---------------------------------------| +-distinct-type-name-+ '-array-type-name----' option-list .-LANGUAGE SQL-. .-NOT DETERMINISTIC-. (1) |--+--------------+--+-------------------+----------------------> '-DETERMINISTIC-----' .-MODIFIES SQL DATA-. .-CALLED ON NULL INPUT-. >--+-------------------+--+----------------------+--------------> +-READS SQL DATA----+ '-CONTAINS SQL------' .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--------------------------------> .-DYNAMIC RESULT SETS 0--------. >--+------------------------------+--+---------------------+----> '-DYNAMIC RESULT SETS--integer-' +-ALLOW DEBUG MODE----+ +-DISABLE DEBUG MODE--+ '-DISALLOW DEBUG MODE-' .-FENCED-----. .-OLD SAVEPOINT LEVEL . >--+------------+--+---------------------+----------------------> '-NOT FENCED-' '-NEW SAVEPOINT LEVEL ' .-COMMIT ON RETURN NO--. >--+----------------------+-------------------------------------> '-COMMIT ON RETURN YES-' >--+---------------------------------------------------------------+--| | .-DEFAULT---------------------. | '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-' | '-U-----------------------' | '-+-WAIT FOR OUTCOME-+--------' '-W----------------'
SQL-routine-body |--+-SQL-control-statement--------------------------+-----------| +-ALLOCATE CURSOR-statement----------------------+ +-ALLOCATE DESCRIPTOR-statement------------------+ +-ALTER FUNCTION-statement-----------------------+ +-ALTER PROCEDURE-statement----------------------+ +-ALTER SEQUENCE-statement-----------------------+ +-ALTER TABLE-statement--------------------------+ +-ASSOCIATE LOCATORS-statement-------------------+ +-COMMENT-statement------------------------------+ +-COMMIT-statement-------------------------------+ +-CONNECT-statement------------------------------+ +-CREATE ALIAS-statement-------------------------+ +-CREATE FUNCTION (External Scalar)-statement----+ +-CREATE FUNCTION (External Table)-statement-----+ +-CREATE FUNCTION (Sourced)-statement------------+ +-CREATE INDEX-statement-------------------------+ +-CREATE PROCEDURE (External)-statement----------+ +-CREATE SCHEMA-statement------------------------+ +-CREATE SEQUENCE-statement----------------------+ +-CREATE TABLE-statement-------------------------+ +-CREATE TYPE-statement--------------------------+ +-CREATE VIEW-statement--------------------------+ +-DEALLOCATE DESCRIPTOR-statement----------------+ +-DECLARE GLOBAL TEMPORARY TABLE-statement-------+ +-DELETE-statement-------------------------------+ +-DESCRIBE-statement-----------------------------+ +-DESCRIBE CURSOR-statement----------------------+ +-DESCRIBE INPUT-statement-----------------------+ +-DESCRIBE PROCEDURE-statement-------------------+ +-DESCRIBE TABLE-statement-----------------------+ +-DISCONNECT-statement---------------------------+ +-DROP-statement---------------------------------+ +-EXECUTE IMMEDIATE-statement--------------------+ +-GET DESCRIPTOR-statement-----------------------+ +-GRANT-statement--------------------------------+ +-INSERT-statement-------------------------------+ +-LABEL-statement--------------------------------+ +-LOCK TABLE-statement---------------------------+ +-MERGE-statement--------------------------------+ +-REFRESH TABLE-statement------------------------+ +-RELEASE-statement------------------------------+ +-RELEASE SAVEPOINT-statement--------------------+ +-RENAME-statement-------------------------------+ +-REVOKE-statement-------------------------------+ +-ROLLBACK-statement-----------------------------+ +-SAVEPOINT-statement----------------------------+ +-SELECT INTO-statement--------------------------+ +-SET CONNECTION-statement-----------------------+ +-SET CURRENT DEBUG MODE-statement---------------+ +-SET CURRENT DECFLOAT ROUNDING MODE-statement---+ +-SET CURRENT DEGREE-statement-------------------+ +-SET CURRENT IMPLICIT XMLPARSE OPTION-statement-+ +-SET DESCRIPTOR-statement-----------------------+ +-SET ENCRYPTION PASSWORD-statement--------------+ +-SET PATH-statement-----------------------------+ +-SET RESULT SETS-statement----------------------+ +-SET SCHEMA-statement---------------------------+ +-SET TRANSACTION-statement----------------------+ +-UPDATE-statement-------------------------------+ '-VALUES INTO-statement--------------------------'
built-in-type |--+-+---SMALLINT---+--------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+-----------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+--------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+-+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+--------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+------------+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+---------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+----------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+-------------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID-----------------------------------------------------------------------------------+ '---XML-------------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer-----------------------------------------------|
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 or array type name is specified, the
database manager searches the SQL path to resolve the schema name
for the distinct type or array 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.
If the routine has a comment or label, they are removed
from the routine definition.
The maximum number of parameters allowed in an SQL procedure is 1024.
SET OPTION DBGVIEW = *SOURCE
For
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), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
General considerations for defining or replacing procedures: See CREATE PROCEDURE for general information about defining a procedure. ALTER PROCEDURE (SQL) allows individual attributes or the routine specification to be altered while preserving the privileges on the procedure.
Alter Procedure Replace considerations: When an SQL procedure definition is replaced, SQL creates a temporary source file that will contain C source code with embedded SQL statements. A program object is then created using the CRTPGM command. The SQL options used to create the program are the options that are in effect at the time the ALTER PROCEDURE (SQL) statement is executed. The program is created with ACTGRP(*CALLER).
When an SQL procedure is altered, a new *PGM
or *SRVPGM object is created and the procedure's attributes are stored
in the created program object. If the *PGM or *SRVPGM
object is saved and then restored to this or another system, the catalogs
are automatically updated with those attributes.
The specific name is used as the name of the member in the source file and the name of the program object, if it is a valid system name. If the procedure name is not a valid system name, a unique name is generated. If a source file member with the same name already exists, the member is overlaid. If a module or a program with the same name already exists, the objects are not overlaid, and a unique name is generated. The unique names are generated according to the rules for generating system table names.
Target release considerations: When an SQL procedure definition is replaced, the target release will be the current release in which the ALTER statement is executed unless the user explicitly specifies a different target release. The target release can be explicitly specified using the TGTRLS keyword in the SET OPTION statement. If the ALTER is specified in the source for a RUNSQLSTM or CRTSQLxxx command, the TGTRLS keyword can also be specified on the command.
If the procedure definition is not replaced, the target release of the existing procedure will be preserved unless the target release level of the procedure is earlier than the earliest supported release level. In this case, the target release will be changed to the earliest supported release level.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Modify the definition for an SQL procedure so that SQL changes are committed on return from the SQL procedure.
ALTER PROCEDURE UPDATE_SALARY_2 ALTER COMMIT ON RETURN YES