<img src="02alter97.gif" height="49" width="100"> ALTER ROUTINE

Informix Guide to SQL: Syntax
SQL Statements

ALTER ROUTINE

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

Syntax

Element Purpose Restrictions Syntax

parameter_type Data type of the parameter The data type (or list of data types) must be the same types (and specified in the same order) as those specified when the UDR was created. Identifier, p. 4-205

routine Name of the UDR to modify The UDR must exist (that is, be registered) in the database. If the name does not uniquely identify a UDR, you must enter one or more appropriate values for parameter_type. Database Object Name, p. 4-50

Element	Purpose	Restrictions	Syntax
parameter_type	Data type of the parameter	The data type (or list of data types) must be the same types (and specified in the same order) as those specified when the UDR was created.	Identifier, p. 4-205
routine	Name of the UDR to modify	The UDR must exist (that is, be registered) in the database. If the name does not uniquely identify a UDR, you must enter one or more appropriate values for parameter_type.	Database Object Name, p. 4-50

Usage

The ALTER ROUTINE statement allows you to modify a previously-defined UDR to tune its performance. With this statement you can modify 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.

Privileges

To use the ALTER ROUTINE statement, you must be the owner of the UDR or have the DBA privilege.

Restrictions

When you use this statement, the type of UDR cannot be ambiguous. The UDR that you specify must refer to either a user-defined function or a user-defined procedure. If either of the following conditions exist, the database server returns an error:

The name (and parameters) that you specify apply to both a user-defined procedure and a user-defined function

The specific name that you specify applies to both a user-defined function and a user-defined procedure

Keywords That Introduce Modifications

Use the following keywords to introduce the items in the UDR that you want to modify.


Keyword	Purpose
`ADD`	Introduces a routine modifier that you want to add to the UDR
MODIFY	Introduces a routine modifier for which you want to modify a value
DROP	Introduces a routine modifier that you want to remove from the UDR
MODIFY EXTERNAL NAME (for external UDRs only)	Introduces a new location for the executable file
WITH	Introduces all modifications

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

ALTER ROUTINE func1 WITH (MODIFY PARALLELIZABLE) 

ALTER ROUTINE func1 WITH (ADD PARALLELIZABLE)

Altering Routine Modifiers Example

Suppose you have an external function func1 that is set to handle null values and has a cost per invocation set to 40. The following ALTER ROUTINE statement adjusts the settings of the function by dropping the ability to handle null values, tunes the func1 by changing the cost per invocation to 20, and indicates that the function can execute in parallel.

ALTER ROUTINE func1(char, int, boolean)
	WITH (
		DROP HANDLESNULLS, 
		MODIFY PERCALL_COST = 20, 
		ADD PARALLELIZABLE
		)

Note also, that because the name func1 is not unique to the database, the data type parameters are specified so that the routine signature would be unique. If this function had a Specific Name, for example, raise_sal, specified when it was created, you could identify the function with the following first line:

ALTER SPECIFIC ROUTINE raise_sal

Related Information

Related Statements: ALTER FUNCTION, ALTER PROCEDURE, CREATE FUNCTION, CREATE PROCEDURE, DROP FUNCTION, DROP PROCEDURE, and DROP ROUTINE

For a discussion on how to create and use SPL routines, see the Informix Guide to SQL: Tutorial.

For a discussion of how to create and use external routines, see Extending Informix Dynamic Server 2000.

For information about how to create C UDRs, see the DataBlade API Programmer's Manual.

Informix Guide to SQL: SyntaxSQL Statements