DB2 10.5 for Linux, UNIX, and Windows

ALTER TYPE (structured) statement

The ALTER TYPE statement is used to add or drop attributes or method specifications of a user-defined structured type. Properties of existing methods can also be altered.

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
  • Owner of the type, as recorded in the OWNER column of the SYSCAT.DATATYPES catalog view
  • DBADM authority
To alter a method 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 method to be fenced, no additional authorities or privileges are required.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-ALTER TYPE--type-name---------------------------------------->

   .----------------------------------------------------------.   
   V                                                          |   
>----+-ADD ATTRIBUTE--| attribute-definition |--------------+-+-><
     |                                 .-RESTRICT-.         |     
     +-DROP ATTRIBUTE--attribute-name--+----------+---------+     
     +-ADD METHOD--| method-specification |-----------------+     
     |                               .--------------------. |     
     |                               V                    | |     
     +-ALTER--| method-identifier |----| method-options |-+-+     
     |                              .-RESTRICT-.            |     
     '-DROP--| method-identifier |--+----------+------------'     

method-identifier

|--+-METHOD--method-name--+-------------------------------+-+---|
   |                      '-(--+---------------------+--)-' |   
   |                           |    .-,---------.    |      |   
   |                           |    V           |    |      |   
   |                           '-(----data-type-+--)-'      |   
   '-SPECIFIC METHOD--specific-name-------------------------'   

method-options

|--+-+-FENCED-----+-----+---------------------------------------|
   | '-NOT FENCED-'     |   
   '-+-THREADSAFE-----+-'   
     '-NOT THREADSAFE-'     

Description

type-name
Identifies the structured type to be changed. It must be an existing type defined in the catalog (SQLSTATE 42704), and the type must be a structured type (SQLSTATE 428DP). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.
ADD ATTRIBUTE
Adds an attribute after the last attribute of the existing structured type.
attribute-definition
Defines the attributes of the structured type.
attribute-name
Specifies a name for the attribute. The name cannot be the same as any other attribute of this structured type (including inherited attributes) or any subtype of this structured type (SQLSTATE 42711).

A number of names used as keywords in predicates are reserved for system use, and may not be used as an attribute-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators.

data-type 1
Specifies the data type of the attribute. It is one of the data types listed under CREATE TABLE, other than XML (SQLSTATE 42601). The data type must identify an existing data type (SQLSTATE 42704). If data-type is specified without a schema name, the type is resolved by searching the schemas on the SQL path. The description of various data types is given in "CREATE TABLE". If the attribute data type is a reference type, the target type of the reference must be a structured type that exists (SQLSTATE 42704).

To prevent type definitions that, at run time, would permit an instance of the type to directly, or indirectly, contain another instance of the same type or one of its subtypes, there is a restriction that a type may not be defined such that one of its attribute types directly or indirectly uses itself (SQLSTATE 428EP).

Character and graphic string data types cannot specify string units of CODEUNITS32.

lob-options
Specifies the options associated with LOB types (or distinct types based on LOB types). For a detailed description of lob-options, see "CREATE TABLE".
DROP ATTRIBUTE
Drops an attribute of the existing structured type.
attribute-name
The name of the attribute. The attribute must exist as an attribute of the type (SQLSTATE 42703).
RESTRICT
Enforces the rule that no attribute can be dropped if type-name is used as the type of an existing table, view, column, attribute nested inside the type of a column, or an index extension.
ADD METHOD method-specification
Adds a method specification to the type identified by type-name. The method cannot be used until a separate CREATE METHOD statement is used to give the method a body. For more information about method-specification, see "CREATE TYPE (Structured)".
ALTER method-identifier
Uniquely identifies an instance of a method that is to be altered. The specified method may or may not have an existing method body. Methods declared as LANGUAGE SQL cannot be altered (SQLSTATE 42917).
method-identifier
METHOD method-name
Identifies a particular method, and is valid only if there is exactly one method instance with the name method-name for the type type-name. The identified method can have any number of parameters defined for it. If no method by this name exists for the type, an error (SQLSTATE 42704) is raised. If there is more than one instance of the method for the type, an error (SQLSTATE 42725) is raised.
METHOD method-name (data-type,...)
Provides the method signature, which uniquely identifies the method. The method resolution algorithm is not used.
method-name
Specifies the name of the method for the type type-name.
(data-type,...)
Values must match the data types that were specified (in the corresponding position) on the CREATE TYPE statement. The number of data types, and the logical concatenation of the data types, is used to identify the specific method instance.

If a data type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

It is not necessary to specify the length, precision, or scale for the parameterized data types. Instead, an empty set of parentheses can be coded to indicate that these attributes are to be ignored when looking for a data type match.

FLOAT() cannot be used (SQLSTATE 42601), because the parameter value indicates different data types (REAL or DOUBLE).

If length, precision, or scale is coded, the value must exactly match that specified in the CREATE TYPE statement.

A type of FLOAT(n) does not need to match the defined value for n, because 0 < n < 25 means REAL, and 24 < n < 54 means DOUBLE. Matching occurs on the basis of whether the type is REAL or DOUBLE.

If no method with the specified signature exists for the type in the named or implied schema, an error (SQLSTATE 42883) is raised.

SPECIFIC METHOD specific-name
Identifies a particular method, using the name that is specified or defaulted to at method creation time. In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements, the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific method instance in the named or implied schema; otherwise, an error (SQLSTATE 42704) is raised.
method-options
Specifies the options that are to be altered for the method.
FENCED or NOT FENCED
Specifies whether the method is considered safe to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED). Most methods have the option of running as FENCED or NOT FENCED.

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

CAUTION:
Use of NOT FENCED for methods 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 methods are used.

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

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

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

THREADSAFE or NOT THREADSAFE
Specifies whether a method is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).
If the method is defined with LANGUAGE other than OLE:
  • If the method is defined as THREADSAFE, the database manager can invoke the method in the same process as other routines. In general, to be threadsafe, a method should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED methods can be THREADSAFE. If the method is defined with LANGUAGE OLE, THREADSAFE may not be specified (SQLSTATE 42613).
  • If the method is defined as NOT THREADSAFE, the database manager will never invoke the method in the same process as another routine. Only a fenced method can be NOT THREADSAFE (SQLSTATE 42613).
DROP method-identifier
Uniquely identifies an instance of a method that is to be dropped. The specified method must not have an existing method body (SQLSTATE 428ER). Use the DROP METHOD statement to drop the method body before using ALTER TYPE DROP METHOD. Methods implicitly generated by the CREATE TYPE statement (such as mutators and observers) cannot be dropped (SQLSTATE 42917).
RESTRICT
Indicates that the specified method is restricted from having an existing method body. Use the DROP METHOD statement to drop the method body before using ALTER TYPE DROP METHOD.

Rules

Notes

Examples