DB2 10.5 for Linux, UNIX, and Windows

ALTER FUNCTION statement

The ALTER FUNCTION statement modifies the properties of an existing function.

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 function
Owner of the function, as recorded in the OWNER column of the SYSCAT.ROUTINES catalog view
DBADM authority

To alter the EXTERNAL NAME of a function, 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 function 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 function to be fenced, no additional authorities or privileges are required.

To alter a function to be SECURED or NOT SECURED the privileges held by the authorization ID of the statement must include at least one of the following authorities:

SECADM authority
CREATE_SECURE_OBJECT authority

If no other clauses are specified, then no other privileges are required to process the statement.

Syntax


>>-ALTER--| function-designator |------------------------------->

   .-----------------------------------.   
   V                                   |   
>----+-EXTERNAL NAME--+-'string'---+-+-+-----------------------><
     |                '-identifier-' |     
     +-+-FENCED-----+----------------+     
     | '-NOT FENCED-'                |     
     +-+-SECURED-----+---------------+     
     | '-NOT SECURED-'               |     
     '-+-THREADSAFE-----+------------'     
       '-NOT THREADSAFE-'                  

function-designator

|--+-FUNCTION--function-name--+-------------------------+-+-----|
   |                          '-(--+---------------+--)-' |   
   |                               | .-,---------. |      |   
   |                               | V           | |      |   
   |                               '---data-type-+-'      |   
   '-SPECIFIC FUNCTION--specific-name---------------------'

Description

function-designator

Uniquely identifies the function to be altered. 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 function. This option can only be specified when altering external functions (SQLSTATE 42849).

FENCED or NOT FENCED

Specifies whether the function is considered safe to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED). Most functions have the option of running as FENCED or NOT FENCED.

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

CAUTION:

Use of NOT FENCED for functions 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 user-defined functions are used.

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

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

This option cannot be altered for LANGUAGE OLE, OLEDB, or CLR functions (SQLSTATE 42849).

SECURED or NOT SECURED

Specifies whether the function is considered secure for row and column access control.

NOT SECURED

Indicates that the function is not considered secure. When the function is invoked, the arguments of the function must not reference a column for which a column mask is enabled and column level access control is activated for its table (SQLSTATE 428HA). This rule applies to the non secure user-defined functions that are invoked anywhere in the statement.

SECURED

Indicates that the function is considered secure.

The function must be secure when it is referenced in a row permission or a column mask (SQLSTATE 428H8).

The function must be secure when it is referenced in a materialized query table and the materialized query table references any table that has row or column level access control activated (SQLSTATE 428H8).

THREADSAFE or NOT THREADSAFE

Specifies whether the function is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).

If the function is defined with LANGUAGE other than OLE and OLEDB:

If the function is defined as THREADSAFE, the database manager can invoke the function in the same process as other routines. In general, to be threadsafe, a function should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED functions can be THREADSAFE.
If the function is defined as NOT THREADSAFE, the database manager will never simultaneously invoke the function in the same process as another routine. Only a fenced function can be NOT THREADSAFE (SQLSTATE 42613).

This option may not be altered for LANGUAGE OLE or OLEDB functions (SQLSTATE 42849).

Notes

It is not possible to alter a function that is in the following schema (SQLSTATE 42832):
- SYSIBM
- SYSFUN
- SYSPROC
Functions declared as LANGUAGE SQL, sourced functions, or template functions cannot be altered (SQLSTATE 42917).
Altering a function from NOT SECURED to SECURED: Normally users with SECADM authority do not have privileges to alter database objects such as user-defined functions and triggers. Typically they will examine the actions taken by a function, ensure it is secure, then grant the CREATE_SECURE_OBJECT authority to someone who has required privileges to alter the user-defined function to be secure. After the function is altered, they will revoke the CREATE_SECURE_OBJECT authority from the user who was granted this authority.
The function is considered secure. The SECURED attribute is considered to be an assertion that declares the user has established a change control audit procedure for all changes to the user-defined function. The database manager assumes that such a control audit procedure is in place for all subsequent ALTER FUNCTION statements or changes to external packages.

Packages and dynamically cached SQL statements that depend on the function might be invalidated because the secure attribute affects the access path selection for statements involving tables for which row or column level access control is activated and the function being replaced.
Altering a function from SECURED to NOT SECURED: The function is considered not secure. Packages and dynamically cached SQL statements that depend on the function might be invalidated because the secure attribute affects the access path selection for statements involving tables for which row or column level access control is activated.
Invoking other user-defined functions in a secure function: When a secure user-defined function is referenced in a data manipulation statement where a row or column access control enforced table is referenced, if the secure user-defined function invokes other user-defined functions, the database manager does not validate whether those nested user-defined functions are secure. If those nested functions can access sensitive data, the user with SECADM authority needs to ensure those functions are allowed to access those data and a change control audit procedure has been established for all changes to those functions.

Example

The function MAIL() has been thoroughly tested. To improve its performance, alter the function to be not fenced.

   ALTER FUNCTION MAIL() NOT FENCED