DB2 10.5 for Linux, UNIX, and Windows

ALTER PROCEDURE (external) statement

The ALTER PROCEDURE (External) statement modifies an existing external 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
To alter the EXTERNAL NAME of a procedure, the privileges held by the authorization ID of the statement must also include at least one of the following authorities:
  • CREATE_EXTERNAL_ROUTINE authority on the database
  • DBADM authority
To alter a procedure to be not fenced, the privileges held by the authorization ID of the statement must also include at least one of the following authorities:
  • CREATE_NOT_FENCED_ROUTINE authority on the database
  • DBADM authority

To alter a procedure to be fenced, no additional authorities or privileges are required.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-ALTER--| procedure-designator |------------------------------>

   .-----------------------------------.   
   V                                   |   
>----+-EXTERNAL NAME--+-'string'---+-+-+-----------------------><
     |                '-identifier-' |     
     +-+-FENCED-----+----------------+     
     | '-NOT FENCED-'                |     
     +-+-EXTERNAL ACTION----+--------+     
     | '-NO EXTERNAL ACTION-'        |     
     +-+-THREADSAFE-----+------------+     
     | '-NOT THREADSAFE-'            |     
     '-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 NAME 'string' or identifier
Identifies the name of the user-written code that implements the procedure.
FENCED or NOT FENCED
Specifies whether the procedure is considered safe to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED). Most procedures have the option of running as FENCED or NOT FENCED.

If a procedure is altered to be FENCED, the database manager insulates its internal resources (for example, data buffers) from access by the procedure. In general, a procedure running as FENCED will not perform as well as a similar one running as NOT FENCED.

CAUTION:
Use of NOT FENCED for procedures that were not adequately coded, reviewed, and tested can compromise the integrity of a DB2® database. DB2 databases take some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED stored procedures are used.

A procedure declared as NOT THREADSAFE cannot be altered to be NOT FENCED (SQLSTATE 42613).

If a procedure has any parameters defined AS LOCATOR, and was defined with the NO SQL option, the procedure cannot be altered to be FENCED (SQLSTATE 42613).

This option cannot be altered for LANGUAGE OLE or CLR procedures (SQLSTATE 42849).

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.
THREADSAFE or NOT THREADSAFE
Specifies whether the procedure is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).
If the procedure is defined with LANGUAGE other than OLE:
  • If the procedure is defined as THREADSAFE, the database manager can invoke the procedure in the same process as other routines. In general, to be threadsafe, a procedure should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED procedures can be THREADSAFE.
  • If the procedure is defined as NOT THREADSAFE, the database manager will never invoke the procedure in the same process as another routine. Only a fenced procedure can be NOT THREADSAFE (SQLSTATE 42613).

This option cannot be altered for LANGUAGE OLE procedures (SQLSTATE 42849).

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

Example

Alter the procedure PARTS_ON_HAND() to be not fenced.
   ALTER PROCEDURE PARTS_ON_HAND() NOT FENCED