SQL Reference

CREATE TYPE (Structured)

The CREATE TYPE statement defines a user-defined structured type. A user-defined structured type may include zero or more attributes. A structured type may be a subtype allowing attributes to be inherited from a supertype. Successful execution of the statement also generates functions to cast between the reference type and its representation type and generates support for the comparison operators (=, <>, <, <=, >, and >=) for use with the reference 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.

Authorization

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

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

If UNDER is specified and authorization ID of the statement is not the same as the definer of the root type of the type hierarchy, then SYSADM or DBADM authority is required.

Syntax

>>-CREATE TYPE--type-name----+-------------------------+-------->
                             '-UNDER--supertype-name---'
 
             .-,-----------------------.
             V                         |
>-----AS--(-----attribute-definition---+---)---*---------------->
 
>----WITHOUT COMPARISONS---*---NOT FINAL---*---MODE DB2SQL---*--><
 
attribute-definition
 
|---attribute-name--| data-type |---*----+------------------+--->
                                         '-| lob-options |--'
 
>----*----+-----------------------+--*--------------------------|
          '-| datalink-options |--'

Description

type-name

Names the type. The name, including the implicit or explicit qualifier, must not identify any other type (built-in, structured, or distinct) already described in the catalog. The unqualified name must not be the same as the name of a built-in data type or BOOLEAN (SQLSTATE 42918). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifer for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names.

A number of names used as keywords in predicates are reserved for system use, and may not be used as a type-name. The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators as described in Basic Predicate. Failure to observe this rule will lead to an error (SQLSTATE 42939).

If a two-part type-name is specified, the schema name cannot begin with "SYS"; otherwise, an error (SQLSTATE 42939) is raised.

UNDER supertype-name

Specifies that this structured type is a subtype under the specified supertype-name. The supertype-name must identify an existing structured type (SQLSTATE 42704). If supertype-name is specified without a schema name, the type is resolved by searching the schemas on the SQL path. The structured type includes all the attributes of the supertype followed by the additional attributes given in the attribute-definition.

attribute-definition

Defines the attributes of the structured type.

attribute-name

The name of an attribute. The name cannot be the same as any other attribute of this structured type or any supertype of this structured type (SQLSTATE 42711).

A number of names used as keywords in predicates are reserved for system use, and may not be used as an attribute-name. The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators as described in Basic Predicate. Failure to observe this rule will lead to an error (SQLSTATE 42939).

data-type

The data type of the attribute. It is one of the data types listed under CREATE TABLE other than LONG VARCHAR, LONG VARGRAPHIC, a distinct type based on LONG VARCHAR or LONG VARGRAPHIC (SQLSTATE 42601). The data type must identify an existing data type (SQLSTATE 42704). If data-type is specified without a schema name, the type is resolved by searching the schemas on the SQL path. The description of various data types is given in CREATE TABLE. If the attribute data type is a reference type, the target type of the reference must be a structured type that exists or is created by this statement (SQLSTATE 42704).

lob-options

Specifies the options associated with LOB types (or distinct types based on LOB types). For a detailed description of lob-options, see CREATE TABLE.

datalink-options

Specifies the options associated with DATALINK types (or distinct types based on DATALINK types). For a detailed description of datalink-options, see CREATE TABLE.

Note that if no options are specified for a DATALINK type or distinct type sourced on DATALINK, LINKTYPE URL and NO LINK CONTROL options are the defaults.

WITHOUT COMPARISONS

Indicates that there are no comparison functions supported for instances of the structured type.

NOT FINAL

Indicates that the structured type may be used as a supertype.

MODE DB2SQL

This clause is used to specify the mode of the type. This is the only value for mode currently supported.

Notes

Creating a structured 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 reference type for any structured type is represented internally by a value of type VARCHAR(16) FOR BIT DATA.
Creation of a structured type automatically generates a set of functions for use with the type. All the functions are generated in the same schema as the structured type. If the signature of the generated function conflicts with the signature of an existing function in this schema, the statement fails (SQLSTATE 42710, SQLCODE -600). The generated functions cannot be dropped without dropping the structured type (SQLSTATE 42917, SQLCODE -658). The following functions are generated:
- Reference Comparisons
  Six comparison functions with names =, <>, <, <=, >, >= are generated for the reference type REF(type-name). Each of these functions takes two parameters of type REF(type-name) and returns true, false, or unknown. The comparison operators for REF(type-name) are defined to have the same behaviour as the comparison operators for the underlying data type of REF(type-name). (68)
  
  The scope of the reference type is not considered in the comparison.
- Cast functions
  Two cast functions are generated to cast between the generated reference type REF(type-name) and the underlying data type of this reference type.
  - The name of the function to cast from the underlying type to the reference type is the name of the type being created.
    The format of this function is:
```
       CREATE FUNCTION type-name (VARCHAR(16) FOR BIT DATA)
                                       RETURNS REF(type-name) ...
```
  - The name of the function to cast from the reference type to the underlying type of the reference type is VARCHAR.
    The format of this function is:
```
  CREATE FUNCTION VARCHAR ( REF(type-name) ) RETURNS VARCHAR(16) FOR BIT DATA
```
The schema name of the structured type must be included in the SQL path (see SET PATH or the FUNCPATH BIND option as described in the Embedded SQL Programming Guide) for successful use of these operators and cast functions in SQL statements.

Examples

Example 1: Create a type for department.

   CREATE TYPE DEPT AS
          (NAME      VARCHAR(32),
           NUMBER    CHAR(6))
        WITHOUT COMPARISONS
        NOT FINAL
        MODE DB2SQL

Example 2: Create a type hierarchy consisting of a type for employees and a subtype for managers.

   CREATE TYPE EMP AS
          (NAME      VARCHAR(32),
           DEPTREF      REF(DEPT),
           SALARY    DECIMAL(10,2) )
        WITHOUT COMPARISONS
        NOT FINAL
        MODE DB2SQL
 
   CREATE TYPE MGR UNDER EMP AS
          (HEADCOUNT INTEGER,
           BUDGET    DECIMAL(10,2) )
        WITHOUT COMPARISONS
        NOT FINAL
        MODE DB2SQL

Footnotes:

(68) All references in a type hierarchy have the same reference representation type. This enables REF(S) and REF(T) to be compared provided that S and T have a common supertype. Since uniqueness of the OID column is enforced only within a table hierarchy, it is possible that a value of REF(T) in one table hierarchy may be "equal" to a value of REF(T) in another table hierarchy, even though they reference different rows.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]