Use the ALTER ROUTINE statement to change the routine modifiers or pathname of a previously defined user-defined routine (UDR), This statement is an extension to the ANSI/ISO standard for SQL.


Element Description Restrictions Syntax
routine User-defined routine to modify Must be registered in the database. If the name does not uniquely identify a routine, 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 routine. Data Type


The ALTER ROUTINE statement allows you to modify a previously defined UDR to tune its performance by modifying characteristics that control how the UDR executes. You can also add or replace related UDRs that provide alternatives for the optimizer, which can improve performance.

This statement is useful when you do not know whether a UDR is a user-defined function or a user-defined procedure. When you use this statement, the database server alters the appropriate user-defined procedure or user-defined function.

All modifications take effect on the next invocation of the UDR.

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