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

(explicit id altpro002) altpro002 (explicit id altpro003) altpro003 (explicit id altpro004) altpro004 (explicit id altpro005) altpro005 ALTER { PROCEDUREprocedure( [ parameter_type ] ) | SPECIFIC PROCEDURE <Specific Name>[] } WITH( { | { ADD | MODIFY | DROP } <Routine Modifier>[] | [] MODIFY EXTERNAL NAME = <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);

See also Example of Altering Routine Modifiers.

1 See Specific Name
2 See Routine modifier
3 External routines only
4 See Shared-Object Filename