A distinct type is a user-defined data type that shares its internal representation with a built-in data type (its "source type"), but is considered to be a separate and incompatible type for most operations. For example, the semantics for a picture type, a text type, and an audio type that all use the built-in data type BLOB for their internal representation are quite different. A distinct type is created using CREATE DISTINCT TYPE.
For example, the following statement creates 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 type that is not comparable to a BLOB or to any other type. This inability to compare AUDIO to other data types allows functions to be created specifically for AUDIO and assures that these functions cannot be applied to other data types (such as pictures or text).
The name of a distinct type is qualified with a schema name. The implicit schema name for an unqualified name depends upon the context in which the distinct type appears. If an unqualified distinct type name is used:
A distinct type does not automatically acquire the functions and operators of its source type, since these may not be meaningful. (For example, the LENGTH function of the AUDIO type might return the length of its object in seconds rather than in bytes.) Instead, distinct types support strong typing. Strong typing ensures that only the functions and operators that are explicitly defined for a distinct type can be applied to that distinct type. However, a function or operator of the source type can be applied to the distinct type by creating an appropriate user-defined function. The user-defined function must be sourced on the existing function that has the source type as a parameter. For example, the following series of SQL statements shows how to create a distinct type named MONEY based on data type DECIMAL(9,2), how to define the + operator for the distinct type, and how the operator might be applied to the distinct type:
CREATE DISTINCT TYPE MONEY AS DECIMAL(9,2) WITH COMPARISONS CREATE FUNCTION "+"(MONEY,MONEY) RETURNS MONEY SOURCE "+"(DECIMAL(9,2),DECIMAL(9,2)) CREATE TABLE SALARY_TABLE (SALARY MONEY, COMMISSION MONEY) SELECT "+"(SALARY, COMMISSION) FROM SALARY_TABLE
A distinct type is subject to the same restrictions as its source type. For example, a table can only have one ROWID column. Therefore, a table with a ROWID column cannot also have a column with distinct type that is sourced on a row ID.
The comparison operators are automatically generated for distinct types, except for distinct types that are sourced on a DataLink. In addition, the database manager automatically generates functions for a distinct type that support casting from the source type to the distinct type and from the distinct type to the source type. For example, for the AUDIO type created above, these are the generated cast functions:
Name of generated cast function | Parameter list | Returns data type |
---|---|---|
schema-name.BLOB | schema-name.AUDIO | BLOB |
schema-name.AUDIO | BLOB | schema-name.AUDIO |
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.