DB2 Version 9.7 for Linux, UNIX, and Windows

Creating distinct types

A user-defined distinct type is a data type derived from an existing type, such as an integer, decimal, or character type. To define a distinct type, you use the CREATE DISTINCT TYPE statement.

Before you begin

Instances of the same distinct type can be compared to each other, if the WITH COMPARISONS clause is specified on the CREATE DISTINCT TYPE statement (as in the example in the procedure). The WITH COMPARISONS clause cannot be specified if the source data type is a large object, LONG VARCHAR, or LONG VARGRAPHIC type.

For the list of privileges required to define distinct types, see the CREATE DISTINCT TYPE statement.

About this task

When you create distinct types, the database manager generates cast functions to cast from the distinct type to the source type, and to cast from the source type to the distinct type. These functions are essential for the manipulation of distinct types in queries. The source type of the distinct type is the data type used by the database manager to internally represent the distinct type. For this reason, it must be a built-in data type. Previously defined distinct types cannot be used as source types of other distinct types.

To define a distinct type, issue the CREATE DISTINCT TYPE statement, specifying a type name and the source type. For example, the following statement defines a new distinct type called T_EDUC that contains SMALLINT values:

   CREATE DISTINCT TYPE T_EDUC AS SMALLINT WITH COMPARISONS

Because the distinct type defined in the preceding statement is based on SMALLINT, the WITH COMPARISONS parameters must be specified.

After you have created a distinct type, you can use it to define columns in a CREATE TABLE statement:

     CREATE TABLE EMPLOYEE
      (EMPNO      CHAR(6)      NOT NULL,
       FIRSTNME   VARCHAR(12)  NOT NULL,
       LASTNAME   VARCHAR(15)  NOT NULL,
       WORKDEPT   CHAR(3),
       PHONENO    CHAR(4),
       PHOTO      BLOB(10M)    NOT NULL,
       EDLEVEL    T_EDUC)
   IN RESOURCE