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)