CREATE TYPE (structured) statement

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.

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 only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

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

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
SCHEMAADM authority on the schema, if the schema name of the type refers to an existing schema
DBADM authority

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, SCHEMAADM authority on the schema containing the root type is required or DBADM authority is required.

Syntax

attribute-definition

rep-type

method-specification

SQL-routine-characteristics

external-routine-characteristics

Description

type-nameNames the type. The name, including the implicit or explicit qualifier, must not identify any other type (built-in, structured, or distinct) that already exists at the current server. The unqualified name must not be the same as the name of a built-in data type or BOOLEAN (SQLSTATE 42918). The unqualified name should also not be ARRAY, INTERVAL, or ROWID. 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 or 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 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).

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-definitionDefines 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 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.

data-type

The data type of the attribute. It is one of the data types listed under CREATE TABLE, other than XML or a weakly typed distinct type (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).

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.

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.

INSTANTIABLE or NOT INSTANTIABLE

Determines whether an instance of the structured type can be created. Implications of not instantiable structured types are:

no constructor function is generated for a non-instantiable type
a non-instantiable type cannot be used as the type of a table or view (SQLSTATE 428DP)
a non-instantiable type can be used as the type of a column (only null values or instances of instantiable subtypes can be inserted into the column.

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.

INLINE LENGTH integer

This option indicates the maximum size (in bytes) of a structured type column instance to store inline with the rest of the values in the row of a table. Instances of a structured type or its subtypes, that are larger than the specified inline length, are stored separately from the base table row, similar to the way that LOB values are handled.

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 formulae that follow, the following terms are used:

short attribute

refers to an attribute with any of the following data types: SMALLINT, INTEGER, BIGINT, REAL, DOUBLE, FLOAT, DATE, or TIME. Also included are distinct types or reference types based on these types.

non-short attribute

refers to an attribute of any of the remaining data types, or distinct types based on those data types.

The system calculates the default inline length as follows:

Determine the added space requirements for non-short attributes using the following formula:
space_for_non_short_attributes = SUM(attributelength + n)
n is defined as:
- 0 bytes for nested structured type attributes
- 2 bytes for non-LOB attributes
- 9 bytes for LOB attributes
attributelength is based on the data type specified for the attribute as shown in Table 1.
Calculate the total default inline length using the following formula:
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 1. Byte Counts for Attribute Data Types
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)

Table 2. LOB Descriptor Size as a Function of the Maximum LOB Length
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

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 required and allows for direct invocation of the constructor function on this type.

WITH FUNCTION ACCESS

Indicates that all methods of this type and its subtypes, including methods created in the future, can be accessed using functional notation. This clause can be specified only for the root type of a structured type hierarchy (the UNDER clause is not specified) (SQLSTATE 42613). This clause is provided to allow the use of functional notation for those applications that prefer this form of notation over method invocation notation.

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). The rep-type cannot be a REAL, FLOAT, DECFLOAT, BLOB, CLOB, DBCLOB, array type, or structured type, and must have a length less than or equal to 32 672 bytes (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).

method-specification

Defines the methods for this type. A method cannot actually be used until it is given a body with a CREATE METHOD statement (SQLSTATE 42884).

OVERRIDING

Specifies that the method being defined overrides a method of a supertype of the type being defined. Overriding enables one to re-implement methods in subtypes, thereby providing more specific functionality. Overriding is not supported for the following types of methods:

Table and row methods
External methods declared with PARAMETER STYLE JAVA
Methods that can be used as predicates in an index extension
System-generated mutator or observer methods

Attempting to override such a method will result in an error (SQLSTATE 42745).

If a method is to be a valid overriding method, there must already exist one original method for one of the proper supertypes of the type being defined, and the following relationships must exist between the overriding method and the original method:

The method name of the method being defined and the original method are equivalent.
The method being defined and the original method have the same number of parameters.
The data type of each parameter of the method being defined and the data type of the corresponding parameters of the original method are identical. This requirement excludes the implicit SELF parameter.

If such an original method does not exist, an error is returned (SQLSTATE 428FV).

The overriding method inherits the following attributes from the original method:

Language
Determinism indication
External action indication
An indication whether this method should be called if any of its arguments is the null value
Result cast (if specified in the original method)
SELF AS RESULT indication
The SQL-data access or CONTAINS SQL indication
For external methods:
- Parameter style
- Locator indication of the parameters and of the result (if specified in the original method)
- FENCED, SCRATCHPAD, FINAL CALL, ALLOW PARALLEL, and DBINFO indication
- INHERIT SPECIAL REGISTER and THREADSAFE indication

method-nameNames the method being defined. It must be an unqualified SQL identifier (SQLSTATE 42601). The method name is implicitly qualified with the schema used for CREATE TYPE.

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.

parameter-nameIdentifies the parameter name. It cannot be SELF, which is the name for the implicit subject parameter of a method (SQLSTATE 42734). If the method is an SQL method, all its parameters must have names (SQLSTATE 42629). If the method being declared overrides another method, the parameter name must be exactly the same as the name of the corresponding parameter of the overridden method; otherwise, an error is returned (SQLSTATE 428FV).

data-type2Specifies the data type of each parameter. One entry in the list must be specified for each parameter that the method will expect to receive. No more than 90 parameters are allowed, including the implicit SELF parameter. If this limit is exceeded, an error is raised (SQLSTATE 54023).

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.

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 (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.

AS LOCATOR

For LOB types or distinct types which are based on a LOB type, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be passed to the method instead of the actual value. This saves greatly in the number of bytes passed to the method, and may save as well in performance, particularly in the case where only a few bytes of the value are actually of interest to the method.

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

RETURNS

This mandatory clause identifies the method's result.

data-type3Specifies the data type of the method's result. In this case, exactly the same considerations apply as for the parameters of methods specified in the description for data-type2.

AS LOCATOR

For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be passed from the method instead of the actual value.

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

data-type4 CAST FROM data-type5

Specifies the data type of the method's result.

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.

AS LOCATOR

For LOB types or distinct types which are based on LOB types, the AS LOCATOR clause can be added. This indicates that a LOB locator is to be passed from the method instead of the actual value.

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

SPECIFIC specific-name

Provides a unique name for the instance of the method that is being defined. This specific name can be used when creating the method body or dropping the method. It can never be used to invoke the method. The unqualified form of specific-name is an SQL identifier (with a maximum length of 18). The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another specific method name that exists at the application server; otherwise an error is raised (SQLSTATE 42710).

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.

SELF AS RESULT

Identifies this method as a type-preserving method, which is defined as follows:

The declared return type must be the same as the declared subject-type (SQLSTATE 428EQ).
When an SQL statement is compiled and resolves to a type preserving method, the static type of the result of the method is the same as the static type of the subject argument.
The method must be implemented in such a way that the dynamic type of the result is the same as the dynamic type of the subject argument (SQLSTATE 2200G), and the result cannot be NULL (SQLSTATE 22004).

If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).

SQL-routine-characteristics

Specifies the characteristics of the method body that will be defined for this type using CREATE METHOD.

LANGUAGE SQL

This clause is used to indicate that the method is written in SQL with a single RETURN statement. The method body is specified using the CREATE METHOD statement.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the SQL method. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII: Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE: Specifies that character data is in UTF-8, and that graphic data is in UCS-2. If the database is not a Unicode database, PARAMETER CCSID UNICODE cannot be specified (SQLSTATE 56031).

NOT DETERMINISTIC or DETERMINISTIC

This optional clause specifies whether the method always returns the same results for given argument values (DETERMINISTIC) or whether the method depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC method must always return the same result from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC. NOT DETERMINISTIC must be explicitly or implicitly specified if the body of the method accesses a special register, or calls another non-deterministic routine (SQLSTATE 428C2).

EXTERNAL ACTION or NO EXTERNAL ACTION

This optional clause specifies whether or not the method takes some action that changes the state of an object not managed by the database manager. Optimizations that assume methods have no external impacts are prevented by specifying EXTERNAL ACTION. For example: sending a message, ringing a bell, or writing a record to a file.

READS SQL DATA or CONTAINS SQL

Specifies the classification of SQL statements that the method can run. The database manager verifies that the SQL statements that the method issues are consistent with this specification.

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.

READS SQL DATA: Specifies that the method can run statements with a data access classification of READS SQL DATA or CONTAINS SQL. The method cannot run SQL statements that modify data (SQLSTATE 42985). Nicknames cannot be referenced in the SQL statement (SQLSTATE 42997).
CONTAINS SQL: Specifies that the method can run only SQL statements with a data access classification of CONTAINS SQL. The method cannot run any SQL statements that read or modify data (SQLSTATE 42985).

CALLED ON NULL INPUT

This optional clause indicates that regardless of whether any arguments are null, the user-defined 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.

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.

INHERIT SPECIAL REGISTERS

This optional clause specifies that updatable special registers in the method will inherit their initial values from the environment of the invoking statement. For a method invoked in the select-statement of a cursor, the initial values are inherited from the environment in which the cursor is opened. For a routine invoked in a nested object (for example a trigger or view), the initial values are inherited from the runtime environment (not inherited from the object definition).

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.

external-routine-characteristics

LANGUAGE

This mandatory clause is used to specify the language interface convention to which the user-defined method body is written.

C: This means the database manager will call the user-defined method as if it were a C function. The user-defined method must conform to the C language calling and linkage convention as defined by the standard ANSI C prototype.
JAVA: This means the database manager will call the user-defined method as a method in a Java™ class.
OLE: This means the database manager will call the user-defined method as if it were a method exposed by an OLE automation object. The method must conform with the OLE automation data types and invocation mechanism as described in the OLE Automation Programmer's Reference.
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).

PARAMETER STYLE

This clause is used to specify the conventions used for passing parameters to and returning the value from methods.

DB2GENERAL: Used to specify the conventions for passing parameters to and returning the value from external methods that are defined as a method in a Java class. This can only be specified when LANGUAGE JAVA is used.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
SQL: Used to specify the conventions for passing parameters to and returning the value from external methods that conform to C language calling and linkage conventions or methods exposed by OLE automation objects. This must be specified when either LANGUAGE C or LANGUAGE OLE is used.

PARAMETER CCSID

Specifies the encoding scheme to use for all string data passed into and out of the external method. If the PARAMETER CCSID clause is not specified, the default is PARAMETER CCSID UNICODE for Unicode databases, and PARAMETER CCSID ASCII for all other databases.

ASCII: Specifies that string data is encoded in the database code page. If the database is a Unicode database, PARAMETER CCSID ASCII cannot be specified (SQLSTATE 56031).
UNICODE: Specifies that character data is in UTF-8, and that graphic data is in UCS-2. If the database is not a Unicode database, PARAMETER CCSID UNICODE cannot be specified (SQLSTATE 56031).

This clause cannot be specified with LANGUAGE OLE (SQLSTATE 42613).

DETERMINISTIC or NOT DETERMINISTIC

This optional clause specifies whether the method always returns the same results for given argument values (DETERMINISTIC) or whether the method depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC method must always return the same result from successive invocations with identical inputs. Optimizations taking advantage of the fact that identical inputs always produce the same results are prevented by specifying NOT DETERMINISTIC. An example of a type that is non-deterministic is one that references special registers, global variables, or non-deterministic functions in a way that affects the result type.

FENCED or NOT FENCED

This clause specifies whether the method is considered "safe" to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED).

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.

CAUTION:

Use of NOT FENCED for methods that were not adequately coded, reviewed, and tested can compromise the integrity of your database. The database engine takes some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED methods are used.

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.

THREADSAFE or NOT THREADSAFE

Specifies whether the method is considered safe to run in the same process as other routines (THREADSAFE), or not (NOT THREADSAFE).

If the method is defined with LANGUAGE other than OLE:

If the method is defined as THREADSAFE, the database manager can invoke the method in the same process as other routines. In general, to be threadsafe, a method should not use any global or static data areas. Most programming references include a discussion of writing threadsafe routines. Both FENCED and NOT FENCED methods can be THREADSAFE.
If the method is defined as NOT THREADSAFE, the database manager will never invoke the method in the same process as another routine.

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

RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT

This optional clause may be used to avoid a call to the external method if any of the non-subject arguments is null.

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 the subject argument is null, in which case the method is not executed and the result is null
If the method is defined to have no parameters, in which case this null argument condition cannot occur.

READS SQL DATA, NO SQL, CONTAINS SQL

Specifies the classification of SQL statements that the method can run. The database manager verifies that the SQL statements that the method issues are consistent with this specification.

For the classification of each statement, see SQL statements that can be executed in routines and triggers.

The default is READS SQL DATA.

READS SQL DATA: Specifies that the method can run statements with a data access classification of READS SQL DATA or CONTAINS SQL (SQLSTATE 38002 or 42985). The method cannot run SQL statements that modify data (SQLSTATE 38003 or 42985).
NO SQL: Specifies that the method can run only SQL statements with a data access classification of NO SQL (SQLSTATE 38001).
CONTAINS SQL: Specifies that the method can run only SQL statements with a data access classification of CONTAINS SQL (SQLSTATE 38004 or 42985). The method cannot run any SQL statements that read or modify data (SQLSTATE 38003 or 42985).

EXTERNAL ACTION or NO EXTERNAL ACTION

NO SCRATCHPAD or SCRATCHPAD length

This optional clause may be used to specify whether a scratchpad is to be provided for an external method. It is strongly recommended that methods be re-entrant, so a scratchpad provides a means for the method to "save state" from one call to the next.

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:

length, if specified, sets the size in bytes of the scratchpad and must be between 1 and 32 767 (SQLSTATE 42820). The default value is 100.
It is initialized to all X'00''s.
Its scope is the SQL statement. There is one scratchpad per reference to the external method in the SQL statement.

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 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 FINAL CALL or FINAL CALL

This optional clause specifies whether a final call is to be made to an external method. The purpose of such a final call is to enable the external method to free any system resources it has acquired. It can be useful in conjunction with the SCRATCHPAD keyword in situations where the external method acquires system resources such as memory and anchors them in the scratchpad.

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:

Normal call: SQL arguments are passed and a result is expected to be returned.
First call: the first call to the external method for this specific reference to the method in this specific SQL statement. The first call is a normal call.
Final call: a final call to the external method to enable the method to free up resources. The final call is not a normal call. This final call occurs at the following times:
- End-of-statement: this case occurs when the cursor is closed for cursor-oriented statements, or when the statement is through executing otherwise.
- End-of-transaction: This case occurs when the normal end-of-statement does not occur. For example, the logic of an application may for some reason bypass the close of the cursor.
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.

ALLOW PARALLEL or DISALLOW PARALLEL

This optional clause specifies whether, for a single reference to the method, the invocation of the method can be parallelized. In general, the invocations of most scalar methods should be parallelizable, but there may be methods (such as those depending on a single copy of a scratchpad) that cannot. If either ALLOW PARALLEL or DISALLOW PARALLEL are specified for a method, then this specification will be accepted.

The following questions should be considered in determining which keyword is appropriate for the method:

Are all the method invocations completely independent of each other? If YES, then specify ALLOW PARALLEL.
Does each method invocation update the scratchpad, providing value(s) that are of interest to the next invocation (the incrementing of a counter, for example)? If YES, then specify DISALLOW PARALLEL or accept the default.
Is there some external action performed by the method which should happen only on one database partition? If YES, then specify DISALLOW PARALLEL or accept the default.
Is the scratchpad used, but only so that some expensive initialization processing can be performed a minimal number of times? If YES, then specify ALLOW PARALLEL.

In any case, the body of every external method should be in a directory that is available on every database partition.

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:

NOT DETERMINISTIC
EXTERNAL ACTION
SCRATCHPAD
FINAL CALL

NO DBINFO or DBINFO

This optional clause specifies whether certain specific information known by the database manager will be passed to the method as an additional invocation-time argument (DBINFO), or not (NO DBINFO).NO DBINFO is the default. DBINFO is not supported for LANGUAGE OLE (SQLSTATE 42613). If the method being defined overrides another method, this clause cannot be specified (SQLSTATE 428FV).

If DBINFO is specified, a structure that contains the following information is passed to the method:

Database name - the name of the currently connected database.
Application ID - unique application ID which is established for each connection to the database.
Application Authorization ID - the application runtime authorization ID, regardless of the nested methods in between this method and the application.
Code page - identifies the database code page.
Schema name - under the exact same conditions as for Table name, contains the name of the schema; otherwise blank.
Table name - if and only if the method reference is either the right side of a SET clause in an UPDATE statement, or an item in the VALUES list of an INSERT statement, contains the unqualified name of the table being updated or inserted; otherwise blank.
Column name - under the exact same conditions as for Table name, contains the name of the column being updated or inserted; otherwise blank.
Database version/release - identifies the version, release and modification level of the database server invoking the method.
Platform - contains the server's platform type.
Table method result column numbers - not applicable to methods.

INHERIT SPECIAL REGISTERS

This optional clause specifies that special registers in the method will inherit their initial values from the calling statement. For cursors, the initial values are inherited from the time that the cursor is opened.

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.

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 type is a root type of a type hierarchy without any attributes.
The addition of a new subtype to a type hierarchy may cause packages to be invalidated. A package may be invalidated if it depends on a supertype of the new type. Such a dependency is the result of the use of a TYPE predicate or a TREAT specification.
A structured type may have no more than 4082 attributes (SQLSTATE 54050).
A method specification is not allowed to have the same signature as a function (comparing the first parameter-type of the function with the subject-type of the method).
No original method may override another method, or be overridden by an original method (SQLSTATE 42745). Furthermore, a function and a method cannot be in an overriding relationship. This means that if the function were considered to be a method with its first parameter as subject S, it must not override another method in any supertype of S, and it must not be overridden by another method in any subtype of S (SQLSTATE 42745).
Creation of a structured type automatically generates a set of functions and methods for use with the type. All the functions and methods are generated in the same schema as the structured type. If the signature of the generated function or method conflicts with or overrides the signature of an existing function in this schema, the statement fails (SQLSTATE 42710). The generated functions or methods cannot be dropped without dropping the structured type (SQLSTATE 42917). The following functions and methods are generated:
- Functions
  - 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 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.
  - 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 funcname1 (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 funcname2 ( 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)
    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.
  - Constructor function
    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.
- Methods
  - Observer methods
    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.
  - Mutator methods
    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:
    - If attribute-type is SMALLINT, the additional mutator supports an argument of type INTEGER.
    - If attribute-type is REAL, the additional mutator supports an argument of type DOUBLE.
    - If attribute-type is CHAR, the additional mutator supports an argument of type VARCHAR.
    - If attribute-type is GRAPHIC, the additional mutator supports an argument of type VARGRAPHIC.
  - If the structured type is used as a column type, the length of an instance of the type can be no more than 1 GB in length at runtime (SQLSTATE 54049).
When creating a new subtype for an existing structured type (for use as a column type), any transform functions already written in support of existing related structured types should be re-examined and updated as necessary. Whether the new type is in the same hierarchy as a given type, or in the hierarchy of a nested type, it is likely that the existing transform function associated with this type will need to be modified to include some or all of the new attributes introduced by the new subtype. Generally speaking, because it is the set of transform functions associated with a given type (or type hierarchy) that enables UDF and client application access to the structured type, the transform functions should be written to support all of the attributes in a given composite hierarchy (that is, including the transitive closure of all subtypes and their nested structured types).
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.
Table access restrictions: If a method is defined as READS SQL DATA, no statement in the method can access a table that is being modified by the statement which invoked the method (SQLSTATE 57053). For example, suppose the method BONUS() is defined as READS SQL DATA. If the statement UPDATE DEPTINFO SET SALARY = SALARY + EMP..BONUS() is invoked, no SQL statement in the BONUS method can read from the EMPLOYEE table.
Privileges: The definer of the user-defined type always receives the EXECUTE privilege WITH GRANT OPTION on all methods and functions automatically generated for the structured type. The EXECUTE privilege is not granted on any methods explicitly specified in the CREATE TYPE statement until a method body is defined using the CREATE METHOD statement. The definer of the user-defined type does have the right to drop the method specification using the ALTER TYPE statement. EXECUTE privilege on all methods and functions automatically generated during the CREATE TYPE (structured) statement is granted to PUBLIC.
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 or EXECUTEIN privilege on the schema containing the packages used by the method.
In a partitioned database environment, the use of SQL in external user-defined functions or methods is not supported (SQLSTATE 42997).
Only routines defined as NO SQL can be used to define an index extension (SQLSTATE 428F8).
A Java routine defined as NOT FENCED will be invoked as if it had been defined as FENCED THREADSAFE.
EXTERNAL ACTION methods: If an EXTERNAL ACTION method is invoked in other than the outermost select list, the results are unpredictable since the number of times the method is invoked will vary depending on the access plan used.
Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2® and with other database products. These alternatives are non-standard and should not be used.
- NOT VARIANT can be specified in place of DETERMINISTIC
- VARIANT can be specified in place of NOT DETERMINISTIC
- NULL CALL can be specified in place of CALLED ON NULL INPUT
- NOT NULL CALL can be specified in place of RETURNS NULL ON NULL INPUT
- PARAMETER STYLE DB2SQL can be specified in place of PARAMETER STYLE SQL
The following syntax is accepted as the default behavior for external methods:
- ASUTIME NO LIMIT
- NO COLLID
- PROGRAM TYPE SUB
- STAY RESIDENT NO
- CCSID UNICODE in a Unicode database
- CCSID ASCII in a non-Unicode database if PARAMETER CCSID UNICODE is not specified
The following syntax is accepted as the default behavior for SQL methods:
- CCSID UNICODE in a Unicode database
- CCSID ASCII in a non-Unicode database

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 a default length is calculated. 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 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

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