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


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

Adding or dropping an attribute is not allowed for type type-name (SQLSTATE 55043) if either:
- The type or one of its subtypes is the type of an existing table or view.
- There exists a column of a table whose type directly or indirectly uses type-name. The terms directly uses and indirectly uses are defined in "Structured types".
- The type or one of its subtypes is used in an index extension.
A type may not be altered by adding attributes so that the total number of attributes for the type, or any of its subtypes, exceeds 4082 (SQLSTATE 54050).
ADD ATTRIBUTE option:
- ADD ATTRIBUTE generates observer and mutator methods for the new attribute. These methods are similar to those generated when a structured type is created (see "CREATE TYPE (Structured)"). If these methods conflict with or override any existing methods or functions, the ALTER TYPE statement fails (SQLSTATE 42745).
- If the INLINE LENGTH for the type (or any of its subtypes) was explicitly specified by the user with a value less than 292, and the attributes added cause the specified inline length to be less than the size of the result of the constructor function for the altered type (32 bytes plus 10 bytes per attribute), then an error results (SQLSTATE 42611).
DROP ATTRIBUTE option:
- An attribute that is inherited from an existing supertype cannot be dropped (SQLSTATE 428DJ).
- DROP ATTRIBUTE drops the mutator and observer methods of the dropped attributes, and checks dependencies on those dropped methods.
DROP METHOD option:
- An original method that is overridden by other methods cannot be dropped (SQLSTATE 42893).

Notes

It is not possible to alter a method that is in the SYSIBM, SYSFUN, or SYSPROC schema (SQLSTATE 42832).
When a type is altered by adding or dropping an attribute, all packages are invalidated that depend on functions or methods that use this type or a subtype of this type as a parameter or a result.
When an attribute is added to or dropped from a structured type:
- If the INLINE LENGTH of the type was calculated by the system when the type was created, the INLINE LENGTH values are automatically modified for the altered type, and all of its subtypes to account for the change. The INLINE LENGTH values are also automatically (recursively) modified for all structured types where the INLINE LENGTH was calculated by the system and the type includes an attribute of any type with a changed INLINE LENGTH.
- If the INLINE LENGTH of any type affected by adding or dropping attributes was explicitly specified by a user, then the INLINE LENGTH for that particular type is not changed. Special care must be taken for explicitly specified inline lengths. If it is likely that a type will have attributes added later on, then the inline length, for any uses of that type or one of its subtypes in a column definition, should be large enough to account for the possible increase in length of the instantiated object.
- If new attributes are to be made visible to application programs, existing transform functions must be modified to match the new structure of the data type.
In a partitioned database environment, the use of SQL in external user-defined functions or methods is not supported (SQLSTATE 42997).
Privileges: The EXECUTE privilege is not given for any methods explicitly specified in the ALTER TYPE statement until a method body is defined using the CREATE METHOD statement. The owner of the user-defined type has the ability to drop the method specification using the ALTER TYPE statement.

Examples

Example 1: The ALTER TYPE statement can be used to permit a cycle of mutually referencing types and tables. Consider mutually referencing tables named EMPLOYEE and DEPARTMENT.

The following sequence would allow the types and tables to be created.

   CREATE TYPE DEPT ...
   CREATE TYPE EMP ... (including attribute named DEPTREF of type REF(DEPT))
   ALTER TYPE DEPT ADD ATTRIBUTE MANAGER REF(EMP)
   CREATE TABLE DEPARTMENT OF DEPT ...
   CREATE TABLE EMPLOYEE OF EMP (DEPTREF WITH OPTIONS SCOPE DEPARTMENT)
   ALTER TABLE DEPARTMENT ALTER COLUMN MANAGER ADD SCOPE EMPLOYEE

The following sequence would allow these tables and types to be dropped.

   DROP TABLE EMPLOYEE   (the MANAGER column in DEPARTMENT becomes unscoped)
   DROP TABLE DEPARTMENT
   ALTER TYPE DEPT DROP ATTRIBUTE MANAGER
   DROP TYPE EMP
   DROP TYPE DEPT

Example 2: The ALTER TYPE statement can be used to create a type with an attribute that references a subtype.

   CREATE TYPE EMP ...
   CREATE TYPE MGR UNDER EMP ...
   ALTER TYPE EMP ADD ATTRIBUTE MANAGER REF(MGR)

Example 3: The ALTER TYPE statement can be used to add an attribute. The following statement adds the SPECIAL attribute to the EMP type. Because the inline length was not specified on the original CREATE TYPE statement, the inline length is recalculated by adding 13 (10 bytes for the new attribute + attribute length + 2 bytes for a non-LOB attribute).
```
   ALTER TYPE EMP ...
     ADD ATTRIBUTE SPECIAL CHAR(1)
```
Example 4: The ALTER TYPE statement can be used to add a method associated with a type. The following statement adds a method called BONUS.
```
   ALTER TYPE EMP ...
     ADD METHOD BONUS (RATE DOUBLE)
       RETURNS INTEGER
       LANGUAGE SQL
       CONTAINS SQL
       NO EXTERNAL ACTION
       DETERMINISTIC
```
Note that the BONUS method cannot be used until a CREATE METHOD statement is issued to create the method body. If it is assumed that type EMP includes an attribute called SALARY, then the following example shows a method body definition.
```
   CREATE METHOD BONUS(RATE DOUBLE) FOR EMP
     RETURN CAST(SELF.SALARY * RATE AS INTEGER)
```