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 generates methods, for retrieving and updating values of attributes. Successful execution of the statement also generates functions, for constructing instances of a structured type used in a column, for casting between the reference type and its representation type, and for supporting the comparison operators (=, <>, <, <=, >, and >=) on the reference type.
The CREATE TYPE statement also defines any method specifications for user-defined methods to be used with the user-defined structured 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:
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 |---+---)--'
.-INSTANTIABLE-----.
>-----+------------------+--*---+-------------------------+--*-->
'-NOT INSTANTIABLE-' '-INLINE LENGTH--integer--'
.-WITHOUT COMPARISONS-. .-NOT FINAL-.
>----+---------------------+---*---+-----------+---*------------>
>----MODE DB2SQL---*----+-----------------------+--*------------>
'-WITH FUNCTION ACCESS--'
>-----+--------------------------+------------------------------>
'-REF USING--| rep-type |--'
>----*--+---------------------------------------+--*------------>
'-CAST (SOURCE AS REF) WITH--funcname1--'
>-----+----------------------------------------+--*------------->
'-CAST (REF AS SOURCE) WITH--funcname2---'
>-----+----------------------------------+---------------------><
| .-,--------------------------. |
| V | |
'----| method-specification |---+--'
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--)------------------------------------------------------'
>---------------------------------------------------------------|
method-specification
|---METHOD--method-name----------------------------------------->
>----(--+----------------------------------------------------------+---)->
| .-,--------------------------------------------------. |
| V | |
'----+-----------------+---data-type2--+-------------+--+--'
'-parameter-name--' '-AS LOCATOR--'
>----*---RETURNS------------------------------------------------>
>-----+-data-type3--+-------------+------------------------+---->
| '-AS LOCATOR--' |
'-data-type4--CAST FROM--data-type5--+-------------+-'
'-AS LOCATOR--'
>----*----+--------------------------+--*----------------------->
'-SPECIFIC--specific-name--'
>-----+-----------------+--*------------------------------------>
'-SELF AS RESULT--'
.-| SQL-routine-characteristics |-------.
>-----+---------------------------------------+--*--------------|
'-| external-routine-characteristics |--'
SQL-routine-characteristics
.-LANGUAGE SQL-. .-NOT DETERMINISTIC--.
|---*---+--------------+---*----+--------------------+--*------->
'-DETERMINISTIC------'
.-NO EXTERNAL ACTION--. .-READS SQL DATA--.
>-----+---------------------+--*----+-----------------+--*------>
'-EXTERNAL ACTION-----' '-CONTAINS SQL----'
.-CALLED ON NULL INPUT--.
>-----+-----------------------+--*------------------------------|
external-routine-characteristics
|---*---LANGUAGE--+-C----+--*---PARAMETER STYLE--+-DB2SQL-----+->
+-JAVA-+ '-DB2GENERAL-'
'-OLE--'
.-NOT DETERMINISTIC--. .-FENCED-----.
>----*----+--------------------+--*----+------------+--*-------->
| (2) | '-NOT FENCED-'
'-DETERMINISTIC------'
.-CALLED ON NULL INPUT------------.
>-----+---------------------------------+--*---NO SQL---*------->
| (3) |
'-RETURNS NULL ON NULL INPUT------'
.-NO EXTERNAL ACTION--. .-NO SCRATCHPAD----------.
>-----+---------------------+--*----+------------------------+-->
'-EXTERNAL ACTION-----' | .-100----. |
'-SCRATCHPAD--+--------+-'
'-length-'
.-NO FINAL CALL--. .-ALLOW PARALLEL----.
>----*----+----------------+--*----+-------------------+--*----->
'-FINAL CALL-----' '-DISALLOW PARALLEL-'
.-NO DBINFO--.
>-----+------------+--*-----------------------------------------|
'-DBINFO-----'
Notes:
Description
The schema name (implicit or explicit) must not be greater than 8 bytes (SQLSTATE 42622).
A number of names used as keywords in predicates are reserved for system use, and cannot be used as a 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 as described in Basic Predicate.
If a two-part type-name is specified, the schema name cannot begin with "SYS"; otherwise, an error (SQLSTATE 42939) is raised.
A number of names used as keywords in predicates are reserved for system use, and cannot be used as an attribute-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 as described in Basic Predicate.
A structured type defined with an attribute of type DATALINK can only be effectively used as the data type for a typed table or typed view (SQLSTATE 01641).
To prevent type definitions that would, at runtime, permit an instance of the type to directly or indirectly contain another instance of the same type or one of its subtypes, a type can not be defined such that one of its attribute types directly or indirectly uses itself (SQLSTATE 428EP). See Structured Types for more information.
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.
To create instances of a non-instantiable type, instantiable subtypes must be created. If NOT INSTANTIABLE is specified, no instance of the new type can be created.
If the specified INLINE LENGTH is smaller than the size of the result of the constructor function for the newly-created type (32 bytes plus 10 bytes per attribute) and smaller than 292 bytes, an error results (SQLSTATE 429B2). Note that the number of attributes includes all attributes inherited from the supertype of the type.
The INLINE LENGTH for the type, whether specified or a default value, is the default inline length for columns that use the structured type. This default can be overridden at CREATE TABLE time.
INLINE LENGTH has no meaning when the structured type is used as the type of a typed table.
The default INLINE LENGTH for a structured type is calculated by the system. In the formula given below, the following terms are used:
The system calculates the default inline length as follows:
space_for_non_short_attributes = SUM(attributelength + n)
n is defined as:
attributelength is based on the data type specified for the attribute as shown in Table 25.
default_length(structured_type) = (number_of_attributes * 10) + 32 + space_for_non-short_attributes
number_of_attributes is the total number of attributes for the structured type, including attributes that are inherited from its supertype. However, number_of_attributes does not include any attributes defined for any subtype of structured_type.
Table 25. Byte Counts for Attribute Data Types
| Attribute Data Type | Byte Count | |
| DECIMAL | The integral part of (p/2)+1, where p is the precision | |
| CHAR(n) | n | |
| VARCHAR(n) | n | |
| GRAPHIC(n) | n * 2 | |
| VARGRAPHIC(n) | n * 2 | |
| TIMESTAMP | 10 | |
| DATALINK(n) | n + 54 | |
| LOB Type | Each LOB attribute has a LOB descriptor in the structured type instance that points to the location of the actual value. The size of the descriptor varies according to the maximum length defined for the LOB attribute | |
| Maximum LOB Length | LOB Descriptor Size | |
| 1 024 | 72 | |
| 8 192 | 96 | |
| 65 536 | 120 | |
| 524 000 | 144 | |
| 4 190 000 | 168 | |
| 134 000 000 | 200 | |
| 536 000 000 | 224 | |
| 1 070 000 000 | 256 | |
| 1 470 000 000 | 280 | |
| 2 147 483 647 | 316 | |
| Distinct Type | Length of the source type of the distinct type | |
| Reference Type | Length of the built-in data type on which the reference type is based. | |
| Structured Type | inline_length(attribute_type) | |
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.
A number of names used as keywords in predicates are reserved for system use, and cannot be used as a method-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 as described in Basic Predicate.
In general, the same name can be used for more than one method if there is some difference in their signatures.
SQL data type specifications and abbreviations which may be specified as a column-type in a CREATE TABLE statement and have a correspondence in the language that is being used to write the method may be specified. Refer to the language-specific sections of the Application Development Guide for details on the mapping between SQL data types and host language data types with respect to user-defined functions and methods.
| Note: | If the SQL data type in question is a structured type, there is no default mapping to a host language data type. A user-defined transform function must be used to create a mapping between the structured type and the host language data type. |
DECIMAL (and NUMERIC) are invalid with LANGUAGE C and OLE (SQLSTATE 42815). For alternatives to using DECIMAL, refer to the Application Development Guide.
REF may be specified, but it does not have a defined scope. Inside the body of the method, a reference-type can be used in a path-expression only by first casting it to have a scope. Similarly, a reference returned by a method can be used in a path-expression only by first casting it to have a scope.
An error is raised (SQLSTATE 42601) if AS LOCATOR is specified for a type other than a LOB or a distinct type based on a LOB.
If the method is FENCED, or if LANGUAGE is SQL, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
An error is raised (SQLSTATE 42601) if AS LOCATOR is specified for a type other than a LOB or a distinct type based on a LOB.
If the method is FENCED, or if LANGUAGE is SQL, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
This clause is used to return a different data type to the invoking statement from the data type returned by the method code. The data-type5 must be castable to the data-type4 parameter. If it is not castable, an error is raised (SQLSTATE 42880).
Since the length, precision or scale for data-type4 can be inferred from data-type5, it not necessary (but still permitted) to specify the length, precision, or scale for parameterized types specified for data-type4. Instead, empty parentheses may be used (VARCHAR(), for example). FLOAT() cannot be used (SQLSTATE 42601), since the parameter value indicates different data types (REAL or DOUBLE).
A distinct type is not valid as the type specified in data-type5 (SQLSTATE 42815).
The cast operation is also subject to runtime checks that might result in conversion errors being raised.
An error is raised (SQLSTATE 42601) if AS LOCATOR is specified for a type other than a LOB or a distinct type based on a LOB.
If the method is FENCED, or if LANGUAGE is SQL, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
The specific-name may be the same as an existing method-name.
If no qualifier is specified, the qualifier that was used for type-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of type-name or an error is raised (SQLSTATE 42882).
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for family compatibility.
LANGUAGE OLE is only supported for user-defined methods stored in Windows 32-bit operating systems.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
Refer to the Application Development Guide for details on passing parameters.
An example of a NOT DETERMINISTIC method would be a method that randomly returns a serial number of an employee in a department. An example of a DETERMINISTIC method would be a method that calculates the area of a polygon.
If a method is registered as FENCED, the database manager insulates its internal resources (data buffers, for example) from access by the method. Most methods will have the option of running as FENCED or NOT FENCED. In general, a method running as FENCED will not perform as well as a similar one running as NOT FENCED.
| Note: | Use of NOT FENCED for methods not adequately checked out can compromise the
integrity of DB2. DB2 takes some precautions against many of the common
types of inadvertent failures that might occur, but cannot guarantee complete
integrity when NOT FENCED user defined methods are used.
While the use of FENCED does offer a greater degree of protection for database integrity than NOT FENCED, a FENCED method that has not been adequately coded, reviewed and tested can also cause an inadvertent failure of DB2. |
Most methods should be able to run either as FENCED or NOT FENCED. Only FENCED can be specified for a method with LANGUAGE OLE (SQLSTATE 42613).
If the method is FENCED, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
To change from FENCED to NOT FENCED, the method must be re-registered, by first dropping it and then recreating it.
Either SYSADM authority, DBADM authority or a special authority (CREATE_NOT_FENCED) is required to register a method as NOT FENCED.
If RETURNS NULL ON NULL INPUT is specified, and if at execution time any one of the method's arguments is null, the method is not called and the result is the null value.
If CALLED ON NULL INPUT is specified, then regardless of the number of null arguments, the method is called. It can return a null value or a normal (non-null) value. However, responsibility for testing for null argument values lies with the method.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility. Similarly, NOT NULL CALL may be used as a synonym for RETURNS NULL ON NULL INPUT.
There are two cases in which this specification is ignored:
If SCRATCHPAD is specified, then at the first invocation of the user-defined method, memory is allocated for a scratchpad to be used by the external method. This scratchpad has the following characteristics:
So, if method X in the following statement is defined with the SCRATCHPAD keyword, three scratchpads would be assigned.
SELECT A, X..(A) FROM TABLEB
WHERE X..(A) > 103 OR X..(A) < 19
If ALLOW PARALLEL is specified or defaulted to, then the scope is different from the above. If the method is executed in multiple partitions, a scratchpad would be assigned in each partition where the method is processed, for each reference to the method in the SQL statement. Similarly, if the query is executed with intra-partition parallelism enabled, more than three scratchpads may be assigned.
The scratchpad is persistent. Its content is preserved from one external method call to the next. Any changes made to the scratchpad by the external method on one call will be present on the next call. The database manager initializes scratchpads at the beginning of execution of each SQL statement. The database manager may reset scratchpads at the beginning of execution of each subquery. The system issues a final call before resetting a scratchpad if the FINAL CALL option is specified.
The scratchpad can be used as a central point for system resources (memory, for example) which the external method might acquire. The method could acquire the memory on the first call, keep its address in the scratchpad, and refer to it in subsequent calls.
In such a case where system resource is acquired, the FINAL CALL keyword should also be specified; this causes a special call to be made at end-of-statement to allow the external method to free any system resources acquired.
If SCRATCHPAD is specified, then on each invocation of the user-defined method, an additional argument is passed to the external method which addresses the scratchpad.
If NO SCRATCHPAD is specified, then no scratchpad is allocated or passed to the external method.
If FINAL CALL is specified, then at execution time, an additional argument is passed to the external method which specifies the type of call. The types of calls are:
If a commit operation occurs while a cursor defined as WITH HOLD is open, a final call is made at the subsequent close of the cursor or at the end of the application.
If NO FINAL CALL is specified, then no "call type" argument is passed to the external method, and no final call is made.
The following questions should be considered in determining which keyword is appropriate for the method:.
In any case, the body of every external method should be in a directory that is available on every partition of the database.
The syntax diagram indicates that the default value is ALLOW PARALLEL. However, the default is DISALLOW PARALLEL if one or more of the following options is specified in the statement:
If DBINFO is specified, then a structure is passed to the method which contains the following information:
Refer to Application Development Guide for detailed information on the structure and how it is passed to the method.
Notes
No method may override, or be overridden by, another method (SQLSTATE 42745). Futhermore, a function and a method may not be in an overriding relationship. This means that if the function were a method with its first parameter as subject S, it must not override another method of any supertype of S, and it must not be overridden by another method of any subtype of S.
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 behavior as the comparison operators for the underlying data type of REF(type-name). 85
The scope of the reference type is not considered in the comparison.
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 format of this function is:
CREATE FUNCTION funcname1 (rep-type)
RETURNS REF(type-name) ...
The format of this function is:
CREATE FUNCTION funcname2 ( REF(type-name) )
RETURNS rep-type ...
For some rep-types, there are additional cast functions generated with funcname1 to handle casting from constants.
CREATE FUNCTION funcname1 (INTEGER)
RETURNS REF(type-name)
CREATE FUNCTION funcname1 ( VARCHAR(n))
RETURNS REF(type-name)
CREATE FUNCTION funcname1 (VARGRAPHIC(n))
RETURNS REF(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.
The constructor function is generated to allow a new instance of the type to be constructed. This new instance will have null for all attributes of the type, including attributes that are inherited from a supertype.
The format of the generated constructor function is:
CREATE FUNCTION type-name ( )
RETURNS type-name
...
If NOT INSTANTIABLE is specified, no constructor function is generated. If the structured type has attributes of type DATALINK, then the invocation of the constructor function fails (SQLSTATE 428ED).
An observer method is defined for each attribute of the structured type. For each attribute, the observer method returns the type of the attribute. If the subject is null, the observer method returns a null value of the attribute type.
For example, the attributes of an instance of the structured type ADDRESS can be observed using C1..STREET, C1..CITY, C1..COUNTRY, and C1..CODE.
The method signature of the generated observer method is as if the following statement had been executed:
CREATE TYPE type-name
...
METHOD attribute-name()
RETURNS attribute-type
where type-name is the structured type name.
A type-preserving mutator method is defined for each attribute of the structured type. Use mutator methods to change attributes within an instance of a structured type. For each attribute, the mutator method returns a copy of the subject modified by assigning the argument to the named attribute of the copy.
For example, an instance of the structured type ADDRESS can be mutated using C1..CODE('M3C1H7'). If the subject is null, the mutator method raises an error (SQLSTATE 2202D).
The method signature of the generated mutator method is as if the following statement had been executed:
CREATE TYPE type-name
...
METHOD attribute-name (attribute-type)
RETURNS type-name
If the attribute data type is SMALLINT, REAL, CHAR, or GRAPHIC, an additional mutator method is generated in order to support mutation using constants:
Examples
Example 1: Create a type for department.
CREATE TYPE DEPT AS
(DEPT NAME VARCHAR(20),
MAX_EMPS INT)
REF USING INT
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),
SERIALNUM INT,
DEPT REF(DEPT),
SALARY DECIMAL(10,2))
MODE DB2SQL
CREATE TYPE MGR UNDER EMP AS
(BONUS DECIMAL(10,2))
MODE DB2SQL
Example 3: Create a type hierarchy for addresses. Addresses are intended to be used as types of columns. The inline length is not specified, so DB2 will calculate a default length. Encapsulate within the address type definition an external method that calculates how close this address is to a given input address. Create the method body using the CREATE METHOD statement.
CREATE TYPE address_t AS
(STREET VARCHAR(30),
NUMBER CHAR(15),
CITY VARCHAR(30),
STATE VARCHAR(10))
NOT FINAL
MODE DB2SQL
METHOD SAMEZIP (addr address_t)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
METHOD DISTANCE (address_t)
RETURNS FLOAT
LANGUAGE C
DETERMINISTIC
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
CREATE TYPE germany_addr_t UNDER address_t AS
(FAMILY_NAME VARCHAR(30))
NOT FINAL
MODE DB2SQL
CREATE TYPE us_addr_t UNDER address_t AS
(ZIP VARCHAR(10))
NOT FINAL
MODE DB2SQL
Example 4: Create a type that has nested structured type attributes.
CREATE TYPE PROJECT AS
(PROJ_NAME VARCHAR(20),
PROJ_ID INTEGER,
PROJ_MGR MGR,
PROJ_LEAD EMP,
LOCATION ADDR_T,
AVAIL_DATE DATE)
MODE DB2SQL