The CREATE TYPE statement defines a user-defined structured type.
A user-defined structured type can include zero or more attributes. A structured type can 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.
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).
If UNDER is specified, and the authorization ID of the statement is not the same as the owner of the root type of the type hierarchy, DBADM authority is required.
>>-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 |-' rep-type |--+-+-SMALLINT----+---------------------------------------------+--| | +-+-INTEGER-+-+ | | | '-INT-----' | | | '-BIGINT------' | | .-(5,0)-------------------. | +-+-+-DECIMAL-+-+--+-------------------------+----------------+ | | '-DEC-----' | | .-,0-------. | | | '-+-NUMERIC-+-' '-(integer-+----------+-)-' | | '-NUM-----' '-,integer-' | | .-(34)-. | +-DECFLOAT--+------+------------------------------------------+ | '-(16)-' | | .-(1)-------. | +-+-+-CHARACTER-+--+-----------+----------+--+--------------+-+ | | '-CHAR------' '-(integer)-' | '-FOR BIT DATA-' | | '-+-VARCHAR----------------+--(integer)-' | | '-+-CHARACTER-+--VARYING-' | | '-CHAR------' | | .-(1)-------. | '-+-GRAPHIC--+-----------+-+----------------------------------' | '-(integer)-' | '-VARGRAPHIC--(integer)--' method-specification |--+------------+--METHOD--method-name--------------------------> '-OVERRIDING-' >--(--+----------------------------------------------------+--)--> | .-,----------------------------------------------. | | 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-. |--●--+--------------+--●--+------------------------------+-----> '-PARAMETER CCSID--+-ASCII---+-' '-UNICODE-' .-NOT DETERMINISTIC-. .-EXTERNAL ACTION----. >--●--+-------------------+--●--+--------------------+--●-------> '-DETERMINISTIC-----' '-NO EXTERNAL ACTION-' .-READS SQL DATA-. .-CALLED ON NULL INPUT-. >--+----------------+--●--+----------------------+--●-----------> '-CONTAINS SQL---' .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--●-----------------------------| external-routine-characteristics |--●--LANGUAGE--+-C----+--●--PARAMETER STYLE--+-DB2GENERAL-+----> +-JAVA-+ '-SQL--------' '-OLE--' >--●--+------------------------------+--●-----------------------> '-PARAMETER CCSID--+-ASCII---+-' '-UNICODE-' .-NOT DETERMINISTIC-. >--+-------------------+--●-------------------------------------> '-DETERMINISTIC-----' .-FENCED------------------------. >--+-------------------------------+--●-------------------------> +-FENCED--●--+-THREADSAFE-----+-+ | '-NOT THREADSAFE-' | | .-THREADSAFE-. | '-NOT FENCED--●--+------------+-' .-CALLED ON NULL INPUT-------. .-READS SQL DATA-. >--+----------------------------+--●--+----------------+--●-----> '-RETURNS NULL ON NULL INPUT-' +-NO SQL---------+ '-CONTAINS SQL---' .-EXTERNAL ACTION----. .-NO SCRATCHPAD----------. >--+--------------------+--●--+------------------------+--●-----> '-NO EXTERNAL ACTION-' | .-100----. | '-SCRATCHPAD--+--------+-' '-length-' .-NO FINAL CALL-. .-ALLOW PARALLEL----. >--+---------------+--●--+-------------------+--●---------------> '-FINAL CALL----' '-DISALLOW PARALLEL-' .-NO DBINFO-. .-INHERIT SPECIAL REGISTERS-. >--+-----------+--●--+---------------------------+--●-----------| '-DBINFO----'
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.
If a two-part type-name is specified, the schema name must not begin with the characters 'SYS' (SQLSTATE 42939).
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.
To prevent type definitions that would, at run time, permit an instance of the type to directly or indirectly contain another instance of the same type or one of its subtypes, a type cannot be defined such that one of its attribute types directly or indirectly uses itself (SQLSTATE 428EP).
Character and graphic string data types cannot specify string units of CODEUNITS32.
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.
space_for_non_short_attributes = SUM(attributelength + n)
attributelength is based on the data type specified for the attribute as shown in Table 1.
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.
Attribute Data Type | Byte Count |
---|---|
DECIMAL | The integral part of (p / 2) + 1, where p is the precision |
DECFLOAT(n) | If n is 16, the byte count is 8; if n is 34, the byte count is 16 |
CHAR(n) | n |
VARCHAR(n) | n |
GRAPHIC(n) | n * 2 |
VARGRAPHIC(n) | n * 2 |
TIMESTAMP | 10 |
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 (see Table 2. |
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) |
Maximum LOB Length | LOB Descriptor Size |
---|---|
1024 | 68 |
8192 | 92 |
65 536 | 116 |
524 000 | 140 |
4 190 000 | 164 |
134 000 000 | 196 |
536 000 000 | 220 |
1 070 000 000 | 252 |
1 470 000 000 | 276 |
2 147 483 647 | 312 |
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.
In general, the same name can be used for more than one method if there is some difference in their signatures.
You can specify SQL data types and abbreviations that can be specified as a column type in the CREATE TABLE statement, and that have equivalents in the language that is being used to write the method. For details on the mapping between SQL data types and host language data types, see the topic that pertains to your language from the following list of related topics.
DECIMAL (or NUMERIC) and decimal floating-point are invalid with LANGUAGE C and OLE (SQLSTATE 42815).
XML data types cannot be used (SQLSTATE 42815).
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).
If the method being declared overrides another method, the AS LOCATOR indication of the parameter must match exactly the AS LOCATOR indication of the corresponding parameter of the overridden method (SQLSTATE 428FV).
If the method being declared overrides another method, the FOR BIT DATA indication of each parameter must match exactly the FOR BIT DATA indication of the corresponding parameter of the overridden method. (SQLSTATE 428FV).
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).
If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).
If the method overrides another method, data-type3 must be a subtype of the data type of the result of the overridden method if this data type is a structured type; otherwise both data types must be identical (SQLSTATE 428FV).
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 returned (SQLSTATE 42880).
Because the length, precision, or scale for data-type4 can be inferred from data-type5, it is not necessary (but still permitted) to specify the length, precision, or scale for parameterized types specified for data-type4. Instead, empty parentheses can be used, such as VARCHAR(), for example. FLOAT() cannot be used (SQLSTATE 42601), because 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). XML is not valid as the type specified in data-type4 or data-type5 (SQLSTATE 42815).
The cast operation is also subject to runtime checks that might result in conversion errors being returned.
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).
If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).
If the method being defined overrides another method, the FOR BIT DATA clause cannot be specified (SQLSTATE 428FV).
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.
If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).
For the classification of each statement, see SQL statements that can be executed in routines and triggers.
Because the SQL statement supported is the RETURN statement, the distinction has to do with whether the expression is a subquery.
The default is READS SQL DATA.
If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).
NULL CALL can be used as a synonym for CALLED ON NULL INPUT.
No changes to the special registers are passed back to the invoker of the function.
Non-updatable special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore set to their default values.
LANGUAGE OLE is only supported for user-defined methods stored in Windows 32-bit operating systems. THREADSAFE may not be specified for methods defined with LANGUAGE OLE (SQLSTATE 42613).
The value DB2GENRL may be used as a synonym for DB2GENERAL.
This clause cannot be specified with LANGUAGE OLE (SQLSTATE 42613).
If a method is registered as FENCED, the database manager protects 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.
Only FENCED can be specified for a method with LANGUAGE OLE or NOT THREADSAFE (SQLSTATE 42613).
If the method is FENCED and has the NO SQL option, the AS LOCATOR clause cannot be specified (SQLSTATE 42613).
Either SYSADM authority, DBADM authority, or a special authority (CREATE_NOT_FENCED_ROUTINE) is required to register a method as NOT FENCED.
For FENCED methods, THREADSAFE is the default if the LANGUAGE is JAVA. For all other languages, NOT THREADSAFE is the default. If the method is defined with LANGUAGE OLE, THREADSAFE may not be specified (SQLSTATE 42613).
For NOT FENCED methods, THREADSAFE is the default. NOT THREADSAFE cannot be specified (SQLSTATE 42613).
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.
For the classification of each statement, see SQL statements that can be executed in routines and triggers.
The default is READS SQL DATA.
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 one shown previously. If the method is executed on multiple database partitions, a scratchpad would be assigned on each database partition where the method is processed, for each reference to the method in the SQL statement. Similarly, if the query is executed with intrapartition 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.
No changes to the special registers are passed back to the caller of the method.
Some special registers, such as the datetime special registers, reflect a property of the statement currently executing, and are therefore never inherited from the caller.
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). (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. Because 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.)
The scope of the reference type is not considered in the comparison.
CREATE FUNCTION funcname1 (rep-type)
RETURNS REF(type-name) ...
CREATE FUNCTION funcname2 ( REF(type-name) )
RETURNS rep-type ...
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 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.
CREATE FUNCTION type-name ( )
RETURNS type-name
...
If NOT INSTANTIABLE is specified, no constructor
function is generated.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.
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).
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: When a new subtype of an existing type is created, all packages dependent on methods that are defined in supertypes of the type being created, and that are eligible for overriding, are invalidated.
When an external method is used in an SQL statement, the method definer must have the EXECUTE privilege on any packages used by the method.
CREATE TYPE DEPT AS
(DEPT NAME VARCHAR(20),
MAX_EMPS INT)
REF USING INT
MODE DB2SQL
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
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 SQL
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
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