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
>>-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
- It is not possible to alter a procedure that is in the following
schema (SQLSTATE 42832):
Example
Alter the procedure PARTS_ON_HAND()
to be not fenced.
ALTER PROCEDURE PARTS_ON_HAND() NOT FENCED