DB2 10.5 for Linux, UNIX, and Windows

ALTER PROCEDURE (SQL) statement

The ALTER PROCEDURE (SQL) statement modifies an existing SQL procedure by changing the properties of the procedure.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

ALTERIN privilege on the schema of the procedure
Owner of the procedure, as recorded in the OWNER column of the SYSCAT.ROUTINES catalog view
DBADM authority

Syntax


>>-ALTER--| procedure-designator |------------------------------>

   .----------------------------.   
   V                            |   
>----+-+-EXTERNAL ACTION----+-+-+------------------------------><
     | '-NO EXTERNAL ACTION-' |     
     '-NEW SAVEPOINT LEVEL----'     

procedure-designator

|--+-PROCEDURE--procedure-name--+-------------------------+-+---|
   |                            '-(--+---------------+--)-' |   
   |                                 | .-,---------. |      |   
   |                                 | V           | |      |   
   |                                 '---data-type-+-'      |   
   '-SPECIFIC PROCEDURE--specific-name----------------------'

Description

procedure-designator: Identifies the procedure to alter. The procedure-designator must identify a procedure that exists at the current server. The owner of the procedure and all privileges on the procedure are preserved. For more information, see Function, method, and procedure designators.
EXTERNAL ACTION or NO EXTERNAL ACTION: Specifies whether the procedure takes some action that changes the state of an object not managed by the database manager (EXTERNAL ACTION), or not (NO EXTERNAL ACTION). If NO EXTERNAL ACTION is specified, the system can use certain optimizations that assume the procedure has no external impact.
NEW SAVEPOINT LEVEL: Specifies that a new savepoint level is to be created for the procedure. A savepoint level refers to the scope of reference for any savepoint-related statement, as well as to the name space used for comparison and reference of any savepoint names.
The savepoint level for a procedure can only be altered to NEW SAVEPOINT LEVEL.

Rules

It is not possible to alter a procedure that is in the following schema (SQLSTATE 42832):
- SYSIBM
- SYSFUN
- SYSPROC

Example

Alter the procedure MEDIAN_RESULT_SET to indicate that it has no external action.

   ALTER PROCEDURE MEDIAN_RESULT_SET(DOUBLE)
     NO EXTERNAL ACTION