DB2 10.5 for Linux, UNIX, and Windows

CREATE TYPE (distinct) statement

The CREATE TYPE (Distinct) statement defines a distinct type. The distinct type is always sourced on one of the built-in data types and can be defined to use strong type or weak type rules..

Successful execution of the statement that defines a strongly typed distinct type also generates functions to cast between the distinct type and its source type and, optionally, generates support for the comparison operators (=, <>, <, <=, >, and >=) for use with the distinct type. Successful execution of the statement that defines a weakly typed distinct type does not generate any functions.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include as least one of the following authorities:

IMPLICIT_SCHEMA authority on the database, if the schema name of the distinct type does not refer to an existing schema
CREATEIN privilege on the schema, if the schema name of the distinct type refers to an existing schema
DBADM authority

Syntax


>>-CREATE TYPE--distinct-type-name--AS--| source-data-type |---->

   .-WITH STRONG TYPE RULES------------------------------.   
>--+-----------------------------------------------------+-----><
   '-WITH WEAK TYPE RULES--+---------------------------+-'   
                           '-| data-type-constraints |-'     

source-data-type

|--+-| built-in-type |------+-----------------------------------|
   '-| anchored-data-type |-'   

built-in-type

|--+-+-SMALLINT----+----------------------------------------------------------------------+--|
   | +-+-INTEGER-+-+                                                                      |   
   | | '-INT-----' |                                                                      |   
   | '-BIGINT------'                                                                      |   
   |                  .-(5,0)-------------------.                                         |   
   +-+-+-DECIMAL-+-+--+-------------------------+-----------------------------------------+   
   | | '-DEC-----' |  |          .-,0-------.   |                                         |   
   | '-+-NUMERIC-+-'  '-(integer-+----------+-)-'                                         |   
   |   '-NUM-----'               '-,integer-'                                             |   
   |          .-(53)------.                                                               |   
   +-+-FLOAT--+-----------+--+------------------------------------------------------------+   
   | |        '-(integer)-'  |                                                            |   
   | +-REAL------------------+                                                            |   
   | |         .-PRECISION-. |                                                            |   
   | '-DOUBLE--+-----------+-'                                                            |   
   |           .-(34)-.                                                                   |   
   +-DECFLOAT--+------+-------------------------------------------------------------------+   
   |           '-(16)-'                                                                   |   
   |                    .-(1 BYTE)-------------------.                                    |   
   +-+-+-+-CHARACTER-+--+----------------------------+----------+--+------------------+-+-+   
   | | | '-CHAR------'  '-(integer-+-------------+-)-'          |  |              (1) | | |   
   | | |                           +-OCTETS------+              |  '-FOR BIT DATA-----' | |   
   | | |                           '-CODEUNITS32-'              |                       | |   
   | | '-+-VARCHAR----------------+--(integer-+-------------+-)-'                       | |   
   | |   '-+-CHARACTER-+--VARYING-'           +-OCTETS------+                           | |   
   | |     '-CHAR------'                      '-CODEUNITS32-'                           | |   
   | |                                  .-(1M)-----------------------------.            | |   
   | '-+-CLOB------------------------+--+----------------------------------+------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-+-------------+-)-'              |   
   |     '-CHAR------'                             +-K-+ +-OCTETS------+                  |   
   |                                               +-M-+ '-CODEUNITS32-'                  |   
   |                                               '-G-'                                  |   
   |            .-(1)------------------------.                                            |   
   +-+-GRAPHIC--+----------------------------+------+-------------------------------------+   
   | |          '-(integer-+-------------+-)-'      |                                     |   
   | |                     +-CODEUNITS16-+          |                                     |   
   | |                     '-CODEUNITS32-'          |                                     |   
   | +-VARGRAPHIC--(integer-+-------------+-)-------+                                     |   
   | |                      +-CODEUNITS16-+         |                                     |   
   | |                      '-CODEUNITS32-'         |                                     |   
   | |         .-(1M)-----------------------------. |                                     |   
   | '-DBCLOB--+----------------------------------+-'                                     |   
   |           '-(integer-+---+-+-------------+-)-'                                       |   
   |                      +-K-+ +-CODEUNITS16-+                                           |   
   |                      +-M-+ '-CODEUNITS32-'                                           |   
   |                      '-G-'                                                           |   
   |                                  .-(1)-------.                                       |   
   +-+-+-+-NCHAR-------------------+--+-----------+------+-------+------------------------+   
   | | | '-NATIONAL--+-CHAR------+-'  '-(integer)-'      |       |                        |   
   | | |             '-CHARACTER-'                       |       |                        |   
   | | '-+-NVARCHAR-------------------------+--(integer)-'       |                        |   
   | |   +-NCHAR VARYING--------------------+                    |                        |   
   | |   '-NATIONAL--+-CHAR------+--VARYING-'                    |                        |   
   | |               '-CHARACTER-'                               |                        |   
   | |                                      .-(1M)-------------. |                        |   
   | '-+-NCLOB---------------------------+--+------------------+-'                        |   
   |   +-NCHAR LARGE OBJECT--------------+  '-(integer-+---+-)-'                          |   
   |   '-NATIONAL CHARACTER LARGE OBJECT-'             +-K-+                              |   
   |                                                   +-M-+                              |   
   |                                                   '-G-'                              |   
   |                          .-(1M)-------------.                                        |   
   +-+-BLOB----------------+--+------------------+----------------------------------------+   
   | '-BINARY LARGE OBJECT-'  '-(integer-+---+-)-'                                        |   
   |                                     +-K-+                                            |   
   |                                     +-M-+                                            |   
   |                                     '-G-'                                            |   
   '-+-DATE-------------------------+-----------------------------------------------------'   
     +-TIME-------------------------+                                                         
     |            .-(--6--)-------. |                                                         
     '-TIMESTAMP--+---------------+-'                                                         
                  '-(--integer--)-'                                                           

anchored-data-type

|--ANCHOR--+-----------+--+----+--+-variable-name----------+----|
           '-DATA TYPE-'  '-TO-'  '-table-name.column-name-'   

data-type-constraints

|----+----------+----+------------------------------+-----------|
     '-NOT NULL-'    '-CHECK--(--check-condition--)-'

Notes:

The FOR BIT DATA clause can be specified in any order with the other column constraints that follow. The FOR BIT DATA clause cannot be specified with string units CODEUNITS32 (SQLSTATE 42613).

Description

distinct-type-name

Names the distinct type. The name, including the implicit or explicit qualifier, must not identify any other type (built-in or user-defined) that already exists at the current server. The unqualified name must not be the same as the name of a built-in data type or BOOLEAN, BINARY, or VARBINARY (SQLSTATE 42918). The unqualified name should also not be ARRAY, INTERVAL, or ROWID.

In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

A number of names used as keywords in predicates are reserved for system use, and cannot be used as a distinct-type-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH, and the comparison operators.

If a two-part distinct-type-name is specified, the schema name must not begin with the characters 'SYS' (SQLSTATE 42939).

source-data-type

Specifies the data type used as the basis for the internal representation of the distinct type. The data type must be a built-in data type. For more information on built-in data types, see "CREATE TABLE". The source data type cannot be of type BOOLEAN, XML or an ARRAY type (SQLSTATE 42601). For portability of applications across platforms, use the following recommended data type names:

DOUBLE or REAL instead of FLOAT
DECIMAL instead of NUMERIC
VARCHAR, BLOB, or CLOB instead of LONG VARCHAR
VARGRAPHIC or DBCLOB instead of LONG VARGRAPHIC

anchored-data-type

Identifies another object used to determine the data type. The data type of the anchor object is bound by the same limitations that apply when specifying the data type directly.

ANCHOR DATA TYPE TO

Indicates that an anchored data type is used to specify the data type.

variable-name: Identifies a global variable with a data type that is a built-in type other than ROW or CURSOR. The data type of the global variable is used as the source data type for the distinct type.
table-name.column-name: Identifies a column name of an existing table or view with a data type that must be specified as a built-in-type. The data type of the column is used as the source data type for the distinct type.

WITH STRONG TYPE RULES

Specifies that strong typing rules are used for operations where this data type is an operand including assignments and comparisons. This is the default.

WITH WEAK TYPE RULES

Specifies that weak typing rules are used for operations where this data type is an operand including assignments, comparisons, and function resolution. When values of a of a weakly typed distinct type are used, the data type is effectively treated as the specified source-data-type when processing the operation.

data-type-constraints

Defines constraints on the distinct type that are applied when values are assigned or cast to the distinct type.

NOT NULL: Prevents a value with this distinct type from having a null value. If NOT NULL is not specified, a value with this distinct type can have the null value.
CHECK (check-condition): Defines a data type check constraint. At any time, the check-condition must be true or unknown for every value with this data type. The check-conditionis a form of the search-condition that conforms to the rules of table check constraints (SQLSTATE 426211) with the addition that the VALUE keyword is used to reference a value that is assigned or cast to the distinct type in the same way that a column name is referenced in a table check constraint. Note that the check-condition cannot reference global variables.

built-in-type

See "CREATE TABLE" for the description of built-in data types.

Rules

Use of anchored data types: An anchored data type cannot refer to the following objects (SQLSTATE 428HS): a nickname, typed table, typed view, statistical view that is associated with an expression-based index, declared temporary table, row definition that is associated with a weakly typed cursor, object with a code page or collation that is different from the database code page or database collation.

Notes

Privileges: The definer of the user-defined type always receives the EXECUTE privilege WITH GRANT OPTION on all functions automatically generated for the distinct type.
EXECUTE privilege on all functions automatically generated during the CREATE TYPE (Distinct) statement is granted to PUBLIC.
Creating a distinct type with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.

Additional generated functions: When a strongly typed distinct type is created, the following functions are generated to cast to and from the source type:

One function to convert from the distinct type to the source type
One function to convert from the source type to the distinct type
One function to convert from INTEGER to the distinct type if the source type is SMALLINT
One function to convert from VARCHAR to the distinct type if the source type is CHAR
One function to convert from VARGRAPHIC to the distinct type if the source type is GRAPHIC.

In general these functions will have the following format:

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

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

In cases in which the source type is a parameterized type, the function to convert from the distinct type to the source type will have as function name the name of the source type without the parameters (see Table 1 for details). The type of the return value of this function will include the parameters given on the CREATE TYPE (Distinct) statement. The function to convert from the source type to the distinct type will have an input parameter whose type is the source type including its parameters. For example,

   CREATE TYPE T_SHOESIZE AS CHAR(2)
     WITH COMPARISONS

   CREATE TYPE T_MILES AS DOUBLE
     WITH COMPARISONS

will generate the following functions:

   FUNCTION CHAR (T_SHOESIZE) RETURNS CHAR (2)

   FUNCTION T_SHOESIZE (CHAR (2))
   RETURNS T_SHOESIZE

   FUNCTION DOUBLE (T_MILES) RETURNS DOUBLE

   FUNCTION T_MILES (DOUBLE) RETURNS T_MILES

The schema of the generated cast functions is the same as the schema of the distinct type. No other function with this name and with the same signature may already exist in the database (SQLSTATE 42710).

The following table gives the names of the functions to convert from the distinct type to the source type and from the source type to the distinct type for all predefined data types.

Table 1. CAST functions on distinct types
Source Type Name	Function Name	Parameter	Return-type
SMALLINT	distinct-type-name	SMALLINT	distinct-type-name
SMALLINT	distinct-type-name	INTEGER	distinct-type-name
SMALLINT	SMALLINT	distinct-type-name	SMALLINT
INTEGER	distinct-type-name	INTEGER	distinct-type-name
INTEGER	INTEGER	distinct-type-name	INTEGER
BIGINT	distinct-type-name	BIGINT	distinct-type-name
BIGINT	BIGINT	distinct-type-name	BIGINT
DECIMAL	distinct-type-name	DECIMAL (p,s)	distinct-type-name
DECIMAL	DECIMAL	distinct-type-name	DECIMAL (p,s)
NUMERIC	distinct-type-name	DECIMAL (p,s)	distinct-type-name
NUMERIC	DECIMAL	distinct-type-name	DECIMAL (p,s)
REAL	distinct-type-name	REAL	distinct-type-name
REAL	distinct-type-name	DOUBLE	distinct-type-name
REAL	REAL	distinct-type-name	REAL
FLOAT(n) where n<=24	distinct-type-name	REAL	distinct-type-name
FLOAT(n) where n<=24	distinct-type-name	DOUBLE	distinct-type-name
FLOAT(n) where n<=24	REAL	distinct-type-name	REAL
FLOAT(n) where n>24	distinct-type-name	DOUBLE	distinct-type-name
FLOAT(n) where n>24	DOUBLE	distinct-type-name	DOUBLE
FLOAT	distinct-type-name	DOUBLE	distinct-type-name
FLOAT	DOUBLE	distinct-type-name	DOUBLE
DOUBLE	distinct-type-name	DOUBLE	distinct-type-name
DOUBLE	DOUBLE	distinct-type-name	DOUBLE
DOUBLE PRECISION	distinct-type-name	DOUBLE	distinct-type-name
DOUBLE PRECISION	DOUBLE	distinct-type-name	DOUBLE
DECFLOAT	distinct-type-name	DECFLOAT(`n`)	distinct-type-name
DECFLOAT	DECFLOAT	distinct-type-name	DECFLOAT(`n`)
CHAR	distinct-type-name	CHAR (n)	distinct-type-name
CHAR	CHAR	distinct-type-name	CHAR (n)
CHAR	distinct-type-name	VARCHAR (n)	distinct-type-name
VARCHAR	distinct-type-name	VARCHAR (n)	distinct-type-name
VARCHAR	VARCHAR	distinct-type-name	VARCHAR (n)
CLOB	distinct-type-name	CLOB (n)	distinct-type-name
CLOB	CLOB	distinct-type-name	CLOB (n)
GRAPHIC	distinct-type-name	GRAPHIC (n)	distinct-type-name
GRAPHIC	GRAPHIC	distinct-type-name	GRAPHIC (n)
GRAPHIC	distinct-type-name	VARGRAPHIC (n)	distinct-type-name
VARGRAPHIC	distinct-type-name	VARGRAPHIC (n)	distinct-type-name
VARGRAPHIC	VARGRAPHIC	distinct-type-name	VARGRAPHIC (n)
DBCLOB	distinct-type-name	DBCLOB (n)	distinct-type-name
DBCLOB	DBCLOB	distinct-type-name	DBCLOB (n)
BLOB	distinct-type-name	BLOB (n)	distinct-type-name
BLOB	BLOB	distinct-type-name	BLOB (n)
DATE	distinct-type-name	DATE	distinct-type-name
DATE	DATE	distinct-type-name	DATE
TIME	distinct-type-name	TIME	distinct-type-name
TIME	TIME	distinct-type-name	TIME
TIMESTAMP	distinct-type-name	TIMESTAMP(p	distinct-type-name
TIMESTAMP	TIMESTAMP	distinct-type-name	TIMESTAMP(p)
Note: NUMERIC and FLOAT are not recommended when creating a user-defined type for a portable application. DECIMAL and DOUBLE should be used instead.

The functions described in the preceding table and the comparison operator functions are the only functions that are generated automatically when distinct types are defined. Consequently, none of the built-in functions (AVG, MAX, LENGTH, and so on) are supported for strongly typed distinct types until the CREATE FUNCTION statement is used to register user-defined functions for the strongly typed distinct type, and those user-defined functions are sourced on the appropriate built-in functions. In particular, note that it is possible to register user-defined functions that are sourced on the built-in column functions.

When a strongly typed distinct type is created, system-generated comparison operators are created when the source type supports comparisons . Creation of these comparison operators will generate entries in the SYSCAT.ROUTINES catalog view for the new functions.

The schema name of the distinct type must be included in the SQL path or the FUNCPATH BIND option for successful use of these operators and cast functions in SQL statements.

When a weakly typed distinct type is created, no additional functions need to be generated or created because the weak type rules allow a weakly typed distinct type to be used in the same context where the source type can be used.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are non-standard and should not be used.
- CREATE DISTINCT TYPE can be specified in place of CREATE TYPE
- The LONG VARCHAR and LONG VARGRAPHIC data types and cast functions are supported but are deprecated and might be removed in a future release. The WITH COMPARISONS clause continues to not support the LONG VARCHAR and LONG VARGRAPHIC data types.
- The WITH COMPARISONS clause, which specifies that system-generated comparison operators are to be created for comparing two instances of the distinct type, can be specified as the last clause of the statement if WITH WEAK TYPE RULES is not specified. Use WITH COMPARISONS only if it is required for compatibility with earlier versions of products in the DB2 family. If the source data type is either BLOB, CLOB, or DBCLOB and WITH COMPARISONS is specified, a warning occurs as in previous releases.
- ALLOW NULL, or just NULL, can be specified as the opposite of NOT NULL. This is the default nullability characteristic of the distinct type if neither the ALLOW NULL clause nor the NOT NULL clause are specified. Specification of ALLOW NULL is not considered to define a data type constraint for the distinct type.

Examples

Example 1: Create a strongly typed distinct type named SHOESIZE that is based on an INTEGER data type.
```
   CREATE TYPE SHOESIZE AS INTEGER
```
This will also result in the creation of comparison operators (=, <>, <, <=, >, >=) and cast functions INTEGER(SHOESIZE) returning INTEGER and SHOESIZE(INTEGER) returning SHOESIZE.
Example 2: Create a strongly typed distinct type named MILES that is based on a DOUBLE data type.
```
   CREATE TYPE MILES AS DOUBLE
```
This will also result in the creation of comparison operators (=, <>, <, =, >, >=) and cast functions DOUBLE(MILES) returning DOUBLE and MILES(DOUBLE) returning MILES.
Example 3: Create a weakly typed distinct type named BONUS that is based on an INTEGER data type and represents a percentage which cannot exceed 100.
```
   CREATE TYPE BONUS AS INTEGER WITH WEAK TYPE RULES
         CHECK(VALUE >= 0 AND VALUE <= 100)
```
Because it is defined with weak type rules, comparison and cast functions are not generated for the weakly typed distinct type called BONUS.
Example 4: Create a weakly typed distinct type named SALARY that is based on a DOUBLE data type which cannot be NULL and where the upper range is limited to less than one hundred thousand.
```
   CREATE TYPE SALARY AS DOUBLE WITH WEAK TYPE RULES
         NOT NULL CHECK(VALUE < 100000)
```