Creating a distinct type

Use the Create Distinct Type window to create a distinct type, also known as a user-defined type. A distinct type must be sourced on one of the DB2 built-in types.

A distinct type can be useful when you need a table column to hold data of a particular type. For example, you might want a table that contains data on sales in different states and have a column for the zip codes where the sales are made. You could define a distinct type called ZIP that would be based on the built-in CHARACTER type with a length of 5, since zip codes are five digits long.

When you create the distinct type, DB2 also creates:

• A cast function to convert instances of the distinct type into instances of its source type.
• A cast function to convert instances of the source type into instances of its distinct type.
• As appropriate, support for the use of comparison operators with the distinct type.

A cast function is a function used to convert instances of a (source) data type into instances of a different (target) data type. In general, a cast function has the name of the target data type.

Authorities and privileges

To create a distinct type:

1. Open the Create Distinct Type window.
2. Optional: Use the Distinct type schema box to specify the schema to own this distinct type that you're creating. If you do not specify a name, the DB2 subsystem user identifier that is used for this connection to the DB2 subsystem is used.
3. In the Distinct type name field, type a name for this distinct type. The name must not be the same as:
   • The name of a built-in data type
   • BOOLEAN
   • Any of the keywords in predicates reserved for system use (ALL, AND, ANY, BETWEEN, DISTINCT, EXCEPT, EXISTS, FALSE, FOR, FROM, IN, IS, LIKE, MATCH, NOT, NULL, ONLY, OR, OVERLAPS, SIMILAR, SOME, TABLE, TRUE, TYPE, UNIQUE, UNKNOWN) or comparison operators (=,<=,<,<>,>,>=)

   Restriction: The name must be unique within the distinct type's schema. No other object in the schema can have the same name as the distinct type.

4. Use the Source data type box to specify the DB2 built-in data type that your distinct type will be based on.
5. Optional: If you specified a data type of CHARACTER, VARCHAR, BLOB, CLOB, DBCLOB, GRAPHIC, or VARGRAPHIC, use the Length field to specify the length (byte count) of the distinct type. The default length is 1 character.

   If you specified a data type of BLOB, CLOB, or DBCLOB, specify a multiplier ( Bytes, K-Bytes, M-Bytes, or G-Bytes) for the length of your distinct type.

6. Optional: If you specified a data type of DECIMAL, use the Precision and Scale fields to specify the precision (total number of digits) for the column and the scale (number of digits to the right of the decimal) for the column. The default precision is 5 and the default scale is 0.
7. Optional: If you specified a data type of CHARACTER, VARCHAR, or CLOB, select a radio button to indicate the subtype for the distinct type's data:

   SBCS

   Single-byte data.

   MIXED

   Mixed data. MIXED can be specified only if the DB2 installation allows mixed data.

   BIT

   BIT data. You cannot specify BIT for a CLOB column.

8. Optional: If you specified a string data type (CHARACTER, VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, DBCLOB), select a radio button to indicate the encoding scheme to be used for the distinct type's data:

   EBCDIC

   Data is encoded using EBCDIC coded character sets.

   ASCII

   Data is encoded using ASCII coded character sets.

9. Optional: In the Comment field, type a 1- to 254-character (including embedded blanks) comment to document the distinct type.
10. Click OK to create your distinct type and close the Create Distinct Type window.

Related information

• Creating a table