ALTER PROCEDURE statement

Use the ALTER PROCEDURE statement to change the routine modifiers or pathname of a previously defined external procedure. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

Notes:

¹ See Specific Name
² See Routine modifier
³ External routines only
⁴ See Shared-Object Filename


Element	Description	Restrictions	Syntax
`procedure`	User-defined procedure to modify	Must be registered in the database. If the name does not uniquely identify a function, you must enter one or more appropriate values for `parameter_type`.	Identifier
`parameter_type`	Data type of a parameter	Must be the same data types (and specified in the same order) as in the definition of `procedure`.	Data Type

Usage

The ALTER PROCEDURE statement enables you to modify an external procedure to tune its performance by modifying characteristics that control how it executes. You can also add or replace related UDRs that provide alternatives for the optimizer, which can improve performance. All modifications take effect on the next invocation of the procedure.

Only the UDR owner or the DBA can use the ALTER PROCEDURE statement.

If the procedure name is not unique among routines registered in the database, you must enter one or more appropriate values for parameter_type.

The following keywords introduce what you want to modify in procedure.


Keyword	Effect
ADD	Add a new routine modifier to the UDR
MODIFY	Change an attribute of a routine modifier
DROP	Delete a routine modifier from the UDR
MODIFY EXTERNAL NAME (for external procedures only)	Replace the file specification of the executable file. When the IFX_EXTEND_ROLE configuration parameter = ON, this option is valid only for users to whom the DBSA has granted the EXTEND role. With IFX_EXTEND_ROLE = OFF (or not set), the UDR owner or the DBA can use this option.
MODIFY EXTERNAL NAME (for external procedures only)	Replace the file specification of the executable file. (Valid only for users who have the EXTEND role)
WITH	Introduces all modifications

If the routine modifier is a BOOLEAN value, MODIFY sets the value to be T (equivalent to using the keyword ADD to add the routine modifier). For example, both of the following statements alter the proc1 procedure so that it can be executed in parallel in the context of a parallelizable data query:

ALTER PROCEDURE proc1 WITH (MODIFY PARALLELIZABLE); 
ALTER PROCEDURE proc1 WITH (ADD PARALLELIZABLE);