ALTER ROUTINE

Use the ALTER ROUTINE statement to change the routine modifiers or pathname of a previously defined user-defined routine (UDR).

Only Dynamic Server supports this statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax

                                 .-,------------------.
                                 V                    |
>>-ALTER--+-ROUTINE--routine--(----+----------------+-+--)-+---->
          |                        '-parameter_type-'      |
          |                                     (1)        |
          '-SPECIFIC ROUTINE--| Specific Name |------------'
 
         .-,------------------------------------------------------------------.
         V                                    (2)                             |
>--WITH(---+-+-ADD----+--| Routine Modifier |-------------------------------+-+-)-><
           | +-MODIFY-+                                                     |
           | '-DROP---'                                                     |
           |  (3)                                                      (4)  |
           '--------MODIFY EXTERNAL NAME =--| Shared-Object Filename |------'

Notes:

See Specific Name
See Routine Modifier
External routines only
See Shared-Object Filename

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.	Database Database Object Name
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	Identifier

Usage

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.