SQL Reference

ALTER TYPE (Structured)

The ALTER TYPE statement is used to add or drop attributes or method specifications of a user-defined structured type.

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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include as least one of the following:

SYSADM or DBADM authority
ALTERIN privilege on the schema of the type.
definer of the type as recorded in the DEFINER column of SYSCAT.DATATYPES

Syntax

>>-ALTER TYPE--type-name---------------------------------------->
 
      .-,----------------------------------------------------------------------------------------.
      V   (1)                                                                                    |
>---------------+-ADD ATTRIBUTE--| attribute-definition |-------------------------------------+--+>
                |                                .-RESTRICT-.                                 |
                +-DROP ATTRIBUTE--attribute-name-+----------+---------------------------------+
                +-ADD--| method-specification |-----------------------------------------------+
                |                                                               .-RESTRICT-.  |
                '-DROP--+-METHOD--method-name--------------------------------+--+----------+--'
                        +-METHOD--method-name--(--+-------------------+---)--+
                        |                         |  .-,-----------.  |      |
                        |                         |  V             |  |      |
                        |                         '----data-type---+--'      |
                        '-SPECIFIC METHOD--specific-name---------------------'
 
>--------------------------------------------------------------><

Notes:

If both attributes and methods are added or dropped, all attribute specifications must occur before all method specifications

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

For a detailed description of attribute-definition, please see CREATE TYPE (Structured).

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 LONG VARCHAR, LONG VARGRAPHIC, or a distinct type based on LONG VARCHAR or LONG VARGRAPHIC (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).

A structured type defined with an attribute of type DATALINK can only be effectively used as the data type for a typed table or type view (SQLSTATE 01641).

To prevent type definitions that, at runtime, 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). See Structured Types for more information.

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.

datalink-options

Specifies the options associated with DATALINK types (or distinct types based on DATALINK types). For a detailed descriptions of datalink-options, see CREATE TABLE.

Note that if no options are specified for a DATALINK type, or distinct type sourced on DATALINK, LINKTYPE URL and NO LINK CONTROL options are the defaults.

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

Adds a method specification to the type identified by the 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).

DROP METHOD

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.

The specified method must be a method that is described in the catalog (SQLSTATE 42704). Methods implicitly generated by the CREATE TYPE statement (such as mutators and observers) cannot be dropped (SQLSTATE 42917).

There are several ways available to identify the method specification to be dropped:

METHOD method-name

Identifies the particular method, and is valid only if there is exactly one method instance with name method-name and subject type type-name. The method thus identified may have any number of parameters. If no method by this name exists for the type type-name, an error is raised (SQLSTATE 42704). If there is more than one method with the name method-name for the named data type, an error is raised (SQLSTATE 42854).

METHOD method-name (data-type,...)

Provides the method signature, which uniquely identifies the method to be dropped. The method selection algorithm is not used.

method-name

The name of the method to be dropped for the specific type. The name must be an unqualified identifier.

(data-type,...)

Must match the data types that were specified in the corresponding positions of the method-specification when the method was defined. The number of data types and the logical concatenation of the data types is used to identify the specific method instance which is to be dropped.

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

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

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

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

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

SPECIFIC METHOD specific-name

Identifies the particular method that is to be dropped, using the specific name either given or defaulted to when the method was defined. If specific-name is an unqualified name, the method is implicitly qualified with the schema of the data type specified for type-name. The specific-name must identify a method for the type type-name; otherwise an error is raised (SQLSTATE 42704).

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 or
- 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, as described in 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.

Notes

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.

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, DB2 recalculates the inline length 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 is an example of a method body definition.

   CREATE METHOD BONUS(RATE DOUBLE) FOR EMP
      RETURN CAST(SELF.SALARY * RATE AS INTEGER)

See CREATE METHOD for a description of this statement.

[ Top of Page | Previous Page | Next Page ]