Distinct types

Distinct types are user-defined data types that are based on existing Db2® built-in data types. Internally, a distinct type shares its representation with an existing data type (the source data type), but is considered to be a separate and incompatible data type.

For example, distinct types can represent various currencies, such as US_Dollar or Canadian_Dollar. Both of these types are represented internally (and in your host language program) as the built-in data type upon which you defined these currencies. For example, if you define both currencies as DECIMAL, they are represented as decimal data types in the system.

Db2 also has built-in data types for storing and manipulating large objects. Your distinct type could be based on one of these large object (LOB) data types, which you might want to use for something like an audio or video stream. The following example illustrates the creation of a distinct type named AUDIO: 
CREATE DISTINCT TYPE AUDIO AS BLOB (1M)
Although AUDIO has the same representation as the built-in data type BLOB, it is considered to be a separate data type that is not comparable to a BLOB or to any other data type. This allows the creation of functions written specifically for AUDIO and assures that these functions will not be applied to any other data type.
Restriction: Not all built-in data types can be used to define distinct types. For example, the XML data type cannot be used to define a distinct type, nor can the array, row or cursor types. For more information, refer to the documentation for the CREATE TYPE (Distinct) statement.
There are several benefits associated with distinct types:
  1. Extensibility: By defining new data types, you can increase the set of types provided by Db2 to support your applications.
  2. Flexibility: You can specify any semantics and behavior for your new data type by using user-defined functions (UDFs) to augment the diversity of the data types available in the system.
  3. Consistent behavior: Strong typing insures that your distinct types will behave appropriately. It guarantees that only functions defined on your distinct type can be applied to instances of the distinct type.
  4. Encapsulation: The set of functions and operators that you can apply to distinct types defines the behavior of your distinct types. This provides flexibility in the implementation since running applications do not depend on the internal representation that you choose for your data type.
  5. Performance: Distinct types are highly integrated into the database manager. Because distinct types are internally represented the same way as built-in data types, they share the same efficient code used to implement components such as built-in functions, comparison operators, and indexes for built-in data types.

Distinct types are identified by qualified identifiers. If the schema name is not used to qualify the distinct type name when used in statements other than CREATE DISTINCT TYPE, DROP DISTINCT TYPE, or COMMENT ON DISTINCT TYPE, the SQL path is searched in sequence for the first schema with a distinct type that matches.

Distinct types sourced on LONG VARCHAR, LONG VARGRAPHIC, or LOB data types are subject to the same restrictions as their source type. However, certain functions and operators of the source data type can be explicitly specified to apply to the distinct type by defining user-defined functions. (These functions are sourced on functions defined on the source data type of the distinct type.) The comparison operators are automatically generated for user-defined distinct types, except those using LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, or DBCLOB as the source type. In addition, functions are generated to support casting from the source data type to the distinct type, and from the distinct type to the source data type.