CREATE DISTINCT TYPE

Invocation

This statement can be embedded in an application program or issued interactively.

Authorization

The privilege set that is defined below must include at least one of the following:

• The CREATEIN privilege for the schema or all schemas
• SYSADM or SYSCTRL authority

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Privilege set

If the statement is embedded in an application program, the privilege set is the privileges that are held by the authorization ID

of the owner of the plan or package.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. The specified distinct type name can include a schema name (a qualifier). However, if the schema name is not the same as the SQL authorization ID, one of the following conditions must be met:

• The privilege set includes SYSADM or SYSCTRL authority.
• The SQL authorization ID of the process has the CREATEIN privilege on the schema.

Syntax

CREATE DISTINCT TYPE distinct-type-name AS source-data-type 
    [WITH COMPARISONS]

source-data-type:

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | 
  {CHARACTER | CHAR} VARYING (integer) | 
  VARCHAR (integer) } 
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}]|
{DATE | TIME | TIMESTAMP} |
ROWID

Description

distinct-type-name

Names the distinct type. The name is implicitly or explicitly qualified by a schema name. The name, together with the implicit or explicit schema name, must not identify a distinct type that exists at the current server.

The unqualified form of distinct-type-name is a long SQL identifier.

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

The unqualified name is implicitly qualified with a schema name according to the following rules:

• If the CREATE DISTINCT TYPE statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
• If the CREATE DISTINCT TYPE statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.

The qualified form of distinct-type-name is a short SQL identifier (the schema name) followed by a period and a long SQL identifier.

The schema name can be 'SYSTOOLS' if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'.

The owner of the distinct type is determined by how the CREATE DISTINCT TYPE statement is invoked:

• If the statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package.
• If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.

Although the information is not recorded in the catalog, the owner is given the USAGE privilege on the distinct type. The owner is also given the EXECUTE privilege with the GRANT option on each of the generated cast functions.

source-data-type

Specifies the data type that is used as the basis for the internal representation of the distinct type. The data type must be a built-in data type. You can use any of the built-in data types that are allowed for the CREATE TABLE statement except LONG VARCHAR or LONG VARGRAPHIC. Use VARCHAR or VARGRAPHIC with an explicit length instead.

If you do not specify a specific value for the data types that have length, precision, or scale attributes (CHAR, GRAPHIC, DECIMAL, NUMERIC, FLOAT), the defaults are as follows:

If the distinct type is sourced on a character string data type, the FOR clause indicates the subtype. If you do not specify the FOR clause, the distinct type is defined with the default subtype. The default is SBCS when the value of field MIXED DATA on installation panel DSNTIPF is NO. The default is MIXED when the value is YES.

If the distinct type is sourced on a string data type, the CCSID clause indicates whether the encoding scheme of the data is ASCII or EBCDIC. If you do not specify CCSID ASCII or CCSID EBCDIC, the encoding scheme is the value of field DEF ENCODING SCHEME on installation panel DSNTIPF.

WITH COMPARISONS

Specifies that system-generated comparison operators are to be created for comparing two instances of the distinct type. Do not specify WITH COMPARISONS if the source data type is BLOB, CLOB, or DBCLOB; otherwise, a warning occurs and the comparison operators are not generated. You must specify WITH COMPARISONS for all other source data types.

DB2 implicitly creates comparison functions for the following comparison operators for use with the distinct type:

The name of the function is the same as the comparison operator. You cannot invoke the comparison functions using function notation syntax, for example, '<'(C1,C2). Instead, use the syntax C1 < C2.

You must not specify the comparison operations for distinct types that are sourced on a CLOB, BLOB, or DBCLOB, or that have a length greater than 255 bytes. (Distinct types that are sourced on a VARCHAR or VARGRAPHIC can have a length greater than 255 bytes).

Source data types with DBCS or mixed data

If you specify a GRAPHIC source type and the ASCII encoding scheme, or a string source type with a mixed data subtype (FOR MIXED DATA), the value of field FOR MIXED DATA on installation panel DSNTIPF must be YES; otherwise, an error occurs. In addition, to create a distinct type on a GRAPHIC data type, the corresponding CCSID must be defined for the implicitly or explicitly specified encoding scheme.

Generated cast functions

The successful execution of the CREATE DISTINCT TYPE statement causes DB2 to generate the following cast functions:

• A function to convert from the distinct type to its source data type
• A function to convert from the source data type to the distinct type
• A function to cast from a data type A to distinct type DT, where A is promotable to the source data type S of distinct type DT

For some source data types, DB2 supports an additional function to convert from:

• INTEGER to the distinct type if the source type is SMALLINT
• VARCHAR to the distinct type if the source type is CHAR
• VARGRAPHIC to the distinct type if the source type is GRAPHIC
• DOUBLE to the distinct type if the source type is REAL

The cast functions are created as if the following statements were executed:

CREATE FUNCTION source-type-name (distinct-type-name) 
    RETURNS source-type-name ...
CREATE FUNCTION distinct-type-name (source-type-name) 
    RETURNS distinct-type-name ...

Even if you specified a length, precision, or scale for the source data type in the CREATE DISTINCT TYPE statement, the name of the cast function that converts from the distinct type to the source type is simply the name of the source data type. The data type of the value that the cast function returns includes any length, precision, or scale values that you specified for the source data type.

The name of the cast function that converts from the source type to the distinct type is the name of the distinct type. The input parameter of the cast function has the same data type as the source data type, including the length, precision, and scale.

For example, assume that a distinct type named T_SHOESIZE is created with the following statement:

CREATE DISTINCT TYPE CLAIRE.T_SHOESIZE AS VARCHAR(2) WITH COMPARISONS

When the statement is executed, DB2 also generates the following cast functions. VARCHAR converts from the distinct type to the source type, and T_SHOESIZE converts from the source type to the distinct type.

FUNCTION CLAIRE.VARCHAR (CLAIRE.T_SHOESIZE) RETURNS SYSIBM.VARCHAR (2)
FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.VARCHAR (2)) RETURNS CLAIRE.T_SHOESIZE

Notice that function VARCHAR returns a value with a data type of VARCHAR(2) and that function T_SHOESIZE has an input parameter with a data type of VARCHAR(2).

The schema of the generated cast functions is the same as the schema of the distinct type. No other function with the same name and function signature must already exist in the database.

In the preceding example, if T_SHOESIZE had been sourced on a SMALLINT, CHAR, or GRAPHIC data type instead of a VARCHAR data type, another cast function would have been generated in addition to the two functions to cast between the distinct type and the source data type. For example, assume that T_SHOESIZE is created with this statement:

CREATE DISTINCT TYPE CLAIRE.T_SHOESIZE AS CHAR(2) WITH COMPARISONS

When the statement is executed, DB2 generates these cast functions:

FUNCTION CLAIRE.CHAR (CLAIRE.T_SHOESIZE) RETURNS SYSIBM.CHAR (2)
FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.CHAR (2)) RETURNS CLAIRE.T_SHOESIZE
FUNCTION CLAIRE.T_SHOESIZE (SYSIBM.VARCHAR (2)) RETURNS CLAIRE.T_SHOESIZE

Notice that the third function enables the casting of a VARCHAR(2) to T_SHOESIZE. This additional function is created to enable casting a constant, such as 'AB', directly to the distinct type. Without the additional function, you would have to first cast 'AB', which has a data type of VARCHAR, to a data type of CHAR and then cast it to the distinct type.

You cannot explicitly drop a generated cast function. The cast functions that are generated for a distinct type are implicitly dropped when the distinct type is dropped with the DROP statement.

Built-in functions

When a distinct type is defined, the built-in functions (such as AVG, MAX, and LENGTH) are not automatically supported for the distinct type. You can use a built-in function on a distinct type only after a sourced user-defined function, which is based on the built-in function, has been created for the distinct type.

CREATE DISTINCT TYPE SHOESIZE AS INTEGER WITH COMPARISONS;

CREATE DISTINCT TYPE MILES AS DOUBLE WITH COMPARISONS;