ALTER METHOD statement

The ALTER METHOD statement modifies an existing method by changing the method body associated with the method.

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 type.
  • SCHEMAADM privilege on the schema of the type.
  • Owner of the type, as recorded in the OWNER column of the SYSCAT.DATATYPES catalog view.
  • DBADM authority.
The privileges held by the authorization ID of the statement must also include one of the following:
  • 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.
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.

Syntax

Read syntax diagramSkip visual syntax diagramALTERmethod-designator EXTERNAL NAME 'string'identifier
method-designator
Read syntax diagramSkip visual syntax diagramMETHODmethod-name(,data-type)FORtype-nameSPECIFIC METHODspecific-name

Description

method-designator
Uniquely identifies the method 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 method. This option can only be specified when altering external methods (SQLSTATE 42849).

Notes

  • It is not possible to alter a method that is in the SYSIBM, SYSFUN, or SYSPROC schema (SQLSTATE 42832).
  • Methods declared as LANGUAGE SQL cannot be altered (SQLSTATE 42917).
  • Methods declared as LANGUAGE CLR cannot be altered (SQLSTATE 42849).
  • The specified method must have a body before it can be altered (SQLSTATE 42704).

Example

Alter the method DISTANCE() in the structured type ADDRESS_T to use the library newaddresslib. 
   ALTER METHOD DISTANCE()
     FOR ADDRESS_T
     EXTERNAL NAME 'newaddresslib!distance2'