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. Successful execution of the statement 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.

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

Syntax

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

>--+----------------------+------------------------------------><
   |                  (1) |   
   '-WITH COMPARISONS-----'   

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------'  |          .-BYTE-.   |          |  '-FOR BIT DATA-' | |   
   | | |                '-(integer-+------+-)-'          |                   | |   
   | | |                                      .-BYTE-.   |                   | |   
   | | '-+-VARCHAR----------------+--(integer-+------+-)-'                   | |   
   | |   '-+-CHARACTER-+--VARYING-'                                          | |   
   | |     '-CHAR------'                                                     | |   
   | |                                  .-(1M)-------------.                 | |   
   | '-+-CLOB------------------------+--+------------------+-----------------' |   
   |   '-+-CHARACTER-+--LARGE OBJECT-'  '-(integer-+---+-)-'                   |   
   |     '-CHAR------'                             +-K-+                       |   
   |                                               +-M-+                       |   
   |                                               '-G-'                       |   
   |            .-(1)-------.                                                  |   
   +-+-GRAPHIC--+-----------+-------+------------------------------------------+   
   | |          '-(integer)-'       |                                          |   
   | +-VARGRAPHIC--(integer)--------+                                          |   
   | |         .-(1M)-------------. |                                          |   
   | '-DBCLOB--+------------------+-'                                          |   
   |           '-(integer-+---+-)-'                                            |   
   |                      +-K-+                                                |   
   |                      +-M-+                                                |   
   |                      '-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-'   

1. Required for all source-data-types except LOBs for which comparisons are not supported.

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 COMPARISONS

Specifies that system-generated comparison operators are to be created for comparing two instances of a distinct type. These keywords should not be specified if the source-data-type is BLOB, CLOB, or DBCLOB, otherwise a warning is returned (SQLSTATE 01596) and the comparison operators will not be generated. For all other source-data-types, the WITH COMPARISONS keywords are required.

Rules

• Use of anchored data types: An anchored data type cannot refer to (SQLSTATE 428HS): a nickname, typed table, typed view, declared temporary table, row definition 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.
• 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.

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

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

   CREATE TYPE T_SHOESIZE AS CHAR(2)
     WITH COMPARISONS

   CREATE TYPE T_MILES AS DOUBLE
     WITH COMPARISONS

   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.

The functions described in the above table 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 distinct types until the CREATE FUNCTION statement is used to register user-defined functions for the 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 distinct type is created using the WITH COMPARISONS clause, system-generated comparison operators are created. 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.

• Syntax alternatives: The following 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.

Examples

   CREATE TYPE SHOESIZE AS INTEGER WITH COMPARISONS

This will also result in the creation of comparison operators (=, <>, <, <=, >, >=) and cast functions INTEGER(SHOESIZE) returning INTEGER and SHOESIZE(INTEGER) returning SHOESIZE.

   CREATE TYPE MILES AS DOUBLE WITH COMPARISONS

This will also result in the creation of comparison operators (=, <>, <, =, >, >=) and cast functions DOUBLE(MILES) returning DOUBLE and MILES(DOUBLE) returning MILES.