CREATE TYPE (Define type)

Function

CREATE TYPE defines an abstract data type.

Privileges

Owner of a schema

A user can define abstract data types that will be owned by that user.

Format

CREATE TYPE [authorization-identifier.]data-type-identifier
            [subtype-clause]
            [default-constructor-option]
            [member-list]
subtype-clause::=UNDER [authorization-identifier.]data-type-identifier
default-constructor-option::=CONSTRUCTOR {PRIVATE|PROTECTED|PUBLIC}

member-list::=(member[,member]...)
  member::={attribute-definition|routine-declaration}
     attribute-definition::=[encapsulation-level]
                          attribute-name data-type [NO SPLIT]
     encapsulation-level::= {PRIVATE|PROTECTED|PUBLIC}
     routine-declaration::=[encapsulation-level] routine-body
     routine-body::={function-body|procedure-body}

Operands

authorization-identifier
Specifies the authorization identifier of the owner of the abstract data type that is being defined.
data-type-identifier
Specifies a name for the abstract data type being defined.

A subtype clause is specified when what is being defined is a subtype that will inherit a specified abstract data type. The subtype clause is used to specify the authorization identifier and data type identifier of the abstract data type that will become the super-type for the abstract data type being defined.

Specifying a subtype clause causes all the attributes and routines defined in the super-type to be inherited by the abstract data type that is being defined.

authorization-identifier.
Specifies the authorization identifier of the owner of the super-type abstract data type.
If the authorization identifier is omitted, the default authorization identifier does not have an abstract data type of the same name, and an abstract data type of the same name exists in the MASTER authorization identifier, that abstract data type is assumed to have been specified.
data-type-identifier
Specifies the super-type abstract data type.

Specifies the encapsulation level for the default constructor function. The default is PRIVATE.

For the default constructor function, a function that has the same name as the defined abstract data type is defined. The default constructor function takes no arguments. This function returns values of an abstract data type in which null values are set for the attributes of the abstract data type.

PRIVATE
Specifies that the default constructor function of the abstract data type can be used only in this abstract data type definition statement.
PROTECTED
Specifies that the abstract data type is partially encapsulated, which means that the default constructor function of the abstract data type can be used only in the abstract data type being defined and in subtype definitions for that abstract data type.
PUBLIC
Specifies that the default constructor function of the abstract data type can be used regardless of the inheritance.

Specifies an attribute that makes up the abstract data type.

encapsulation-level
Specifies one of three encapsulation levels. The encapsulation level can be specified for routines that code operations on attributes and abstract data types. The default is the encapsulation level that is next higher than the encapsulation level in the definition part. If no encapsulation level is specified at the first level, PUBLIC is assumed as the default.
PRIVATE
This option encapsulates the specified attribute, in which case the attribute can be used only in the definition of the abstract data type.
PROTECTED
This option partially encapsulates the specified attribute, in which case the attribute can be used only in the definition of the abstract data type and in the definition of all subtypes of that abstract data type.
PUBLIC
This option allows the specified attribute to be used regardless of any inheritance relationships that may be in effect.
attribute-name
Specifies the name of the attribute of the abstract data type.
data-type
Specifies the data type of the attribute of the abstract data type.
If the specified data type is an abstract data type, no authorization identifier is specified, and the default authorization identifier does not have an abstract data type of the same name, and if there is an abstract data type of the same name in the MASTER authorization identifier, that abstract data type is assumed to have been specified.
The following data types cannot be specified:
  • CHAR or VARCHAR for which a character set is specified
NO SPLIT
When the actual data length of a variable-length character string is at least 256 bytes, specifies that each row is to be stored on a single page.
In some cases, the NO SPLIT option reduces the database storage space requirements. This option is called the no-split option; for details, see the HiRDB Version 9 Installation and Design Guide.
The no-split option is applicable only to the variable-length character string types (VARCHAR, NVARCHAR, and MVARCHAR).

The routine declaration is where the routine in which data manipulations are specified is written. The same encapsulation levels as for attribute definition are applicable to routine declarations.

Write the function or procedure in the routine body. For details about functions and procedures, see CREATE [PUBLIC] FUNCTION (Define function, define public function) or CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure) in this chapter.

Common rules

  1. When an authorization identifier is specified in a routine, it must be the authorization identifier of the user who executes the routine.
  2. The procedure in a CALL statement or the function call function specified in the function body or procedure body must be one of the following:
    • A function or procedure that was already defined outside of the CREATE TYPE statement
    • A function or procedure that was already defined in a routine declaration prior to declaration of this routine
  3. The attribute name of each abstract data type must be unique among all abstract data types that have an interconnected inheritance relationship.
  4. The maximum permissible number of generations for inherited subtypes is 30,000.
  5. An abstract data type to be defined with CREATE TYPE must meet the expressions below. The following table indicates the length of the attributes (data lengths).

    [Figure]

    Table 3-43 Data lengths

    CategoryData typeDate length (bytes)
    Numeric dataINTEGER (4-byte binary integer)4
    SMALLINT (two-byte binary integer)2
    [LARGE] DECIMAL [m, n]# (packed decimal floating-point number)[Figure]m/2[Figure] + 1
    FLOAT (8-byte floating point number)8
    SMALLFLT (4-byte floating point number)4
    Character dataCHARACTER[n] (n-byte fixed-length character string)n
    VARCHAR[n] (n-byte variable-length character string)35
    National character dataNCHAR[n] (n-character fixed-length national character string)2n
    NVARCHAR[n] (n-character variable-length national character string)35
    Mixed character dataMCHAR[n] (n-byte fixed-length mixed character string)n
    MVARCHAR[n] (n-byte variable-length mixed character string)35
    Date dataDATE (date)4
    Date interval dataINTEVAL YEAR TO DAY (date interval)5
    Time dataTIME (time)3
    Time interval dataINTERVAL HOUR TO SECOND (time interval)4
    Time stamp dataTIMESTAMP[p] (fractional second with p digits) (time stamp)7 + p/2
    Large object dataBLOB (binary string in n bytes)35
    Abstract dataUser-defined data type defined in CREATE TYPE35
    m, n: Positive integers.
    p: Integer 0, 2, 4, or 6
    #: This is a fixed-point number with a total of m digits and n decimal places. If m is omitted, the default that is assumed is 15.
  6. When the SQL compile option is specified in an ALTER PROCEDURE or ALTER ROUTINE statement, the length of the SQL statement that is created by incorporating the SQL compile option into the source CREATE PROCEDURE statement of the routine being re-created must not exceed the maximum permissible length for SQL statements.
  7. CREATE TYPE cannot be executed from within a Java procedure if the execution results in invalidation of the SQL object being executed.

Notes

  1. The CREATE TYPE statement cannot be specified from an X/Open-compliant UAP running under OLTP.
  2. The following shows the invalidation conditions for the SQL object associated with the type definition:
    • When you define an abstract data type, if a valid SQL object of a function, procedure, or trigger uses an abstract data type (all subtypes of the highest data type) with the same inheritance relationship as the abstract data type being defined, the SQL object is invalidated.
    • When you define an abstract data type, if the same data type as the one you are defining uses MASTER as its authorization identifier, any valid SQL object that belongs to the authorization identifier of the abstract data type being defined becomes invalid if it is in a function, procedure, or trigger that uses the MASTER authentication identifer. In addition, if a valid SQL object is in a public function or public procedure that has the same authorization identifier as that of the abstract data type being defined, the SQL object in that public function or public procedure becomes invalid.
    • For details about invalidation of an SQL object when specifying a function body and defining a function, see the notes under CREATE [PUBLIC] FUNCTION (Define function, define public function) in this chapter.
    • For details about invalidation of an SQL object when specifying a procedure body and defining a procedure, see the notes under CREATE [PUBLIC] PROCEDURE (Define procedure, define public procedure) in this chapter.
  3. If an SQL object for which functions, procedures, and triggers are in effect is nullified, any rows of nullified functions, procedures, and triggers in the SQL_ROUTINE_RESOURCES dictionary table are deleted.
  4. For a function that was invalidated for any of the reasons in Note 2, if a function satisfying either of the following conditions is used in the view definition, the type definition results in an error:
    • Abstract data type is used in the data type of an argument.
    • Abstract data type is used in the data type of a return value.
  5. Before executing the SQL object for nullified functions, procedures, and triggers, you need to execute ALTER ROUTINE, ALTER PROCEDURE, or ALTER TRIGGER to re-create the SQL object for the functions, procedures, and triggers.
  6. To access a view table that references an invalidated function associated with the type definition, you must execute ALTER ROUTINE to re-create the SQL object of the function.
  7. The subtype of an abstract data type that is provided by the developer of a plug-in cannot be defined by inheriting an abstract data type that is registered in the system through use of the pdplgrgst command.