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
SCHEMAADM authority on the schema of the procedure
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.
SYSADM authority.
DBADM authority if the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBADM.

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
SYSADM authority.
DBADM authority if the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBADM.

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

Note: Db2 11.1.4.7 security special build 41268 includes changes to the implicit authorities of both the SYSADM and the DBADM authorities. By default, the SYSADM authority, instead of the DBADM authority, implicitly has the authorities CREATE_EXTERNAL_ROUTINE and CREATE_NOT_FENCED_ROUTINE. If the DB2_ALTERNATE_AUTHZ_BEHAVIOUR registry variable is set and contains the value EXTERNAL_ROUTINE_DBADM or NOT_FENCED_ROUTINE_DBADM respectively, then the DBADM authority also implicitly has these privileges.

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

Syntax

procedure-designator

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).

This option cannot be altered for a procedure that is registered as a component routine of an aggregate interface function (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.

This option cannot be altered for a procedure that is registered as a component routine of an aggregate interface function (SQLSTATE 42849).

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).

This option cannot be altered for a procedure that is registered as a component routine of an aggregate interface function (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.

This option cannot be altered for a procedure that is registered as a component routine of an aggregate interface function (SQLSTATE 42849).

Rules

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

Example

Alter the procedure PARTS_ON_HAND() to be not fenced.

   ALTER PROCEDURE PARTS_ON_HAND() NOT FENCED