SQL Reference

ALTER TYPE (Structured)

The ALTER TYPE statement is used to add or drop attributes 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                                                 |
>---------+-ADD ATTRIBUTE--| attribute-definition |--+--+------><
          '-DROP ATTRIBUTE--attribute-name-----------'
 

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). The unqualified name must not be the same as the name of a built-in data type or BOOLEAN (SQLSTATE 42918). 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 characteristics of the new attribute. See attribute-definition in CREATE TYPE (Structured).

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

Rules

• A type cannot be altered if it or one of its subtypes is the type of an existing table (SQLSTATE 55043).

Notes

• When a type is altered, all packages are invalidated that depend on functions that use this type or a subtype of this type as a parameter or a result.

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)

[ DB2 List of Books | Search the DB2 Books ]