Description

distinct-type-name
Names the distinct type. The name, including the implicit or explicit qualifier, must not be the same as a distinct type that already exists at the current server.

If SQL names were specified, the distinct type will be created in the schema specified by the implicit or explicit qualifier.

If system names were specified, the distinct type will be created in the schema that is specified by the qualifier. If not qualified:

If the distinct type name is not a valid system name, DB2 UDB for iSeries will generate a system name. For information on the rules for generating a name, see Rules for Table Name Generation.

distinct-type-name must not be the name of a built-in data type, or any of the following system-reserved keywords even if you specify them as delimited identifiers.

= < > >=
<= <> ¬= ¬<
¬< != !< !>
ALL DISTINCT NODENUMBER SOME
AND EXCEPT NODENAME STRIP
ANY EXISTS NOT SUBSTRING
BETWEEN EXTRACT NULL TABLE
BOOLEAN FALSE ONLY THEN
CASE FOR OR TRIM
CAST FROM OVERLAPS TRUE
CHECK HASHED_VALUE PARTITION TYPE
DATAPARTITIONNAME IN POSITION UNIQUE
DATAPARTITIONNUM IS RRN UNKNOWN
DBPARTITIONNAME LIKE SELECT WHEN
DBPARTITIONNUM MATCH SIMILAR

If a qualified distinct-type-name is specified, the schema name cannot be QSYS, QSYS2, QTEMP, or SYSIBM.

built-in-type
Specifies the built-in data type used as the basis for the internal representation of the distinct type. See CREATE TABLE for a more complete description of each built-in data type.

For portability of applications across platforms, use the following recommended data type names:

If you do not specify a specific value for the data types that have length, precision, or scale attributes, the default attributes of the data type as shown in the syntax diagram are implied.

If the distinct type is sourced on a string data type, a CCSID is associated with the distinct data type at the time the distinct type is created. For more information about data types, see CREATE TABLE.

WITH COMPARISONS
Specifies that system-generated comparison functions are to be created for comparing two instances of the distinct type. WITH COMPARISONS is the default. Comparison functions will be generated for all source types with the exception of a DATALINK whether or not WITH COMPARISONS is specified.64 For compatibility with other DB2(R) products, WITH COMPARISONS should be specified.

The comparison functions do not support the LIKE predicate. In order to use the LIKE predicate on a distinct type, it must be cast to a built-in type.


64.
Service programs are not created for these comparison functions. These comparison functions are not registered in the SYSROUTINES catalog table.