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 >=) on 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. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

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 the 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---*----+---------------------------+--*-------->
                        '-REF USING--| rep-type |---'
 
>-----+----------------------------------------+--*------------->
      '-CAST (SOURCE AS REF) WITH--funcname1---'
 
>-----+----------------------------------------+--*------------><
      '-CAST (REF AS SOURCE) WITH--funcname2---'
 
attribute-definition
 
|---attribute-name--| data-type |---*--------------------------->
 
>-----+----------------------+--*-------------------------------|
      +-| lob-options |------+
      '-| datalink-options |-'
 
rep-type
 
|---+-SMALLINT-----------------------------------------------------------------------+->
    +-+-INTEGER-+--------------------------------------------------------------------+
    | '-INT-----'                                                                    |
    +-BIGINT-------------------------------------------------------------------------+
    +--+-DECIMAL-+---+----------------------------------+----------------------------+
    |  +-DEC-----+   '-(--integer--+-------------+---)--'                            |
    |  +-NUMERIC-+                 '-,--integer--'                                   |
    |  '-NUM-----'                                                                   |
    +--+--+-CHARACTER-+---+------------+----------------+---+----------------------+-+
    |  |  '-CHAR------'   '-(integer)--'                |   |  (1)                 | |
    |  '--+-VARCHAR-------------------+--(--integer--)--'   '--------FOR BIT DATA--' |
    |     '--+-CHARACTER-+---VARYING--'                                              |
    |        '-CHAR------'                                                           |
    |                                                                                |
    +-GRAPHIC--+------------+--------------------------------------------------------+
    |          '-(integer)--'                                                        |
    '-VARGRAPHIC--(--integer--)------------------------------------------------------'
 
>---------------------------------------------------------------|
 

Notes:

1. The FOR BIT DATA clause may be specified in random order with the other column constraints that follow.

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 qualifier 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 attribute-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, or 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.

REF USING rep-type

Defines the built-in data type used as the representation (underlying data type) for the reference type of this structured type and all its subtypes. This clause can only be specified for the root type of a structured type hierarchy (UNDER clause is not specified) (SQLSTATE 42613).

If this clause is not specified for the root type of a structured type hierarchy, then REF USING VARCHAR(16) FOR BIT DATA is assumed.

CAST (SOURCE AS REF) WITH funcname1

Defines the name of the system-generated function that casts a value with the data type rep-type to the reference type of this structured type. A schema name must not be specified as part of funcname1 (SQLSTATE 42601). The cast function is created in the same schema as the structured type. If the clause is not specified, the default value for funcname1 is type-name (the name of the structured type). A function signature matching funcname1(rep-type) must not already exist in the same schema (SQLSTATE 42710).

CAST (REF AS SOURCE) WITH funcname2

Defines the name of the system-generated function that casts a reference type value for this structured type to the data type rep-type. A schema name must not be specified as part of funcname2(SQLSTATE 42601). The cast function is created in the same schema as the structured type. If the clause is not specified, the default value for funcname2 is rep-type (the name of the representation type).

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.

• A structured subtype defined with no attributes defines a subtype that inherits all its attributes from the supertype. If neither an UNDER clause nor any other attribute is specified, then the root type of the type hierarchy is defined without any attributes.

• 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). ⁷⁷

    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 implicit or explicit funcname1.

      The format of this function is:

             CREATE FUNCTION funcname2 (rep-type)
                 RETURNS REF(type-name) ...
      

    • The name of the function to cast from the reference type to the underlying type of the reference type is the implicit or explicit funcname2.

      The format of this function is:

        CREATE FUNCTION funcname1 ( REF(type-name) ) 
          RETURNS rep-type ... 
      

    For some rep-types, there are additional cast functions generated with funcname1 to handle casting from constants.

    • If rep-type is SMALLINT, the additional generated cast function has the format:

        CREATE FUNCTION funcname1 (INTEGER) 
          RETURNS REF(type-name)
      

    • If rep-type is CHAR(n), the additional generated cast function has the format:

        CREATE FUNCTION funcname1 ( VARCHAR(n)) 
          RETURNS REF(type-name)
      

    • If rep-type is GRAPHIC(n), the additional generated cast function has the format:

        CREATE FUNCTION funcname1 (VARGRAPHIC(n)) 
          RETURNS REF(type-name)
      

• Constructor

  The constructor function is generated to allow a new instance of the type to be constructed. This function cannot be used in the current version.

  The format of the generated constructor function is:

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

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 Application Development 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))
                MODE DB2SQL

Example 2:  Create a type hierarchy consisting of a type for employees and a subtype for managers. Every employee has a six digit ID number so use a DECIMAL(6,0) as the reference type. The cast functions should be called ID_TO_REF and REF_TO_ID.

   CREATE TYPE EMP AS
          (NAME      VARCHAR(32),
           DEPTREF      REF(DEPT),
           SALARY    DECIMAL(10,2) )
                MODE DB2SQL
	REF USING DECIMAL (6,0)
	CAST (SOURCE AS REF) WITH ID_TO_REF
	CAST (REF AS SOURCE) WITH REF_TO_ID
 
   CREATE TYPE MGR UNDER EMP AS
          (HEADCOUNT INTEGER,
           BUDGET    DECIMAL(10,2) )
                MODE DB2SQL

⁷⁷

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.

[ DB2 List of Books | Search the DB2 Books ]