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

Read syntax diagramSkip visual syntax diagram
>>-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:
  1. 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

Notes

Examples