Application Development Guide

Defining a Distinct Type

Distinct types, like other objects such as tables, indexes, and UDFs, need to be defined with a CREATE statement.

Use the CREATE DISTINCT TYPE statement to define your new distinct type. Detailed explanations for the statement syntax and all its options are found in the SQL Reference.

For the CREATE DISTINCT TYPE statement, note that:

1. The name of the new distinct type can be a qualified or an unqualified name. If it is qualified by a schema different from the authorization ID of the statement, you must have DBADM authority on the database.
2. The source type of the distinct type is the type used by DB2 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.
3. The WITH COMPARISONS clause is used to tell DB2 that functions to support the comparison operations on instances of the distinct type should be generated by DB2. This clause is required if comparison operations are supported on the source type (for example, INTEGER and DATE) and is prohibited if comparison operations are not supported (for example, LONG VARCHAR and BLOB).

• Cast from the distinct type to the source type, using the standard name of the source type. For example, if you create a distinct type based on FLOAT, the cast function called DOUBLE is created.
• Cast from the source type to the distinct type. Refer to the SQL Reference for a discussion of when additional casts to the distinct types are generated.

These functions are important for the manipulation of distinct types in queries.