Application Development Guide

Defining a UDT

UDTs, 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 UDT. 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 UDT 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 UDT is the type used by DB2 to internally represent the UDT. For this reason, it must be a built-in data type. Previously defined UDTs cannot be used as source types of other UDTs.

3. The WITH COMPARISONS clause is used to tell DB2 that functions to support the comparison operations on instances of the UDT 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 UDT 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 UDT. Refer to the SQL Reference for a discussion of when additional casts to the UDTs are generated.

These functions are important for the manipulation of UDTs in queries.

[ DB2 List of Books | Search the DB2 Books ]