The ALTER PROCEDURE (External) statement alters an external 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 different external program is specified, the privileges held by the authorization ID of the statement must also include the same privileges required to create a new external procedure. For more information, see CREATE PROCEDURE (External).
For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure, Corresponding System Authorities When Checking Privileges to a Table or View, 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-------------------------------------->< parameter-type |----data-type--+------------+----------------------------------| '-AS LOCATOR-' data-type |--+-built-in-type------+---------------------------------------| +-distinct-type-name-+ '-array-type-name----'
option-list |--+-----------------------+------------------------------------> '-LANGUAGE--+-C-------+-' +-C++-----+ +-CL------+ +-COBOL---+ +-COBOLLE-+ +-JAVA----+ +-PLI-----+ +-REXX----+ +-RPG-----+ '-RPGLE---' >--+----------------------------------------+-------------------> '-+-PARAMETER STYLE SQL----------------+-' +-PARAMETER STYLE DB2SQL-------------+ +-PARAMETER STYLE GENERAL------------+ +-PARAMETER STYLE GENERAL WITH NULLS-+ +-PARAMETER STYLE JAVA---------------+ '-PARAMETER STYLE DB2GENERAL---------' (1) >--+-------------------+------+-------------------+-------------> +-NOT DETERMINISTIC-+ +-MODIFIES SQL DATA-+ '-DETERMINISTIC-----' +-READS SQL DATA----+ +-CONTAINS SQL------+ '-NO SQL------------' .-CALLED ON NULL INPUT-. .-INHERIT SPECIAL REGISTERS-. >--+----------------------+--+---------------------------+------> >--+------------------------------+--+-----------+--------------> '-DYNAMIC RESULT SETS--integer-' +-NO DBINFO-+ '-DBINFO----' >--+---------------------+--+------------+----------------------> +-ALLOW DEBUG MODE----+ +-FENCED-----+ +-DISABLE DEBUG MODE--+ '-NOT FENCED-' '-DISALLOW DEBUG MODE-' >--+--------------------------------------+---------------------> '-EXTERNAL NAME--external-program-name-' >--+---------------------+--+----------------------+------------| +-OLD SAVEPOINT LEVEL + +-COMMIT ON RETURN NO--+ '-NEW SAVEPOINT LEVEL ' '-COMMIT ON RETURN YES-'
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 is preserved. If the external program or service program exists at the time the procedure is altered, 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.
Specifies that the procedure is defined to receive
a locator for this parameter. If AS LOCATOR is specified, the data
type must be a LOB or XML or a distinct type based on a LOB or XML.
General considerations for defining or changing a procedure: See CREATE PROCEDURE for general information about defining a procedure. ALTER PROCEDURE (External) allows individual attributes to be altered while preserving the privileges on the procedure.
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 procedure MYPROC to change the name of the external program that is invoked when the procedure is called. The name of the external program is PROG10A.
ALTER PROCEDURE MYPROC EXTERNAL NAME PROG10A