DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE TYPE (structured) statement

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 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:
  • 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
  • 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, DBADM authority is required.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-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----'                                        

Description

type-name
Names the type. The name, including the implicit or explicit qualifier, must not identify any other type (built-in, structured, or distinct) that already exists at the current server. The unqualified name must not be the same as the name of a built-in data type, BINARY, VARBINARY, 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-definition
Defines the attributes of the structured type.
attribute-name
The name of an attribute. The attribute-name cannot be the same as any other attribute of this structured type or any supertype of this structured type (SQLSTATE 42711).

A number of names used as keywords in predicates are reserved for system use, and 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 (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).

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 formula given below, 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:
  1. 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.

  2. 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-name
Names 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-name
Identifies 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-type2
Specifies 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 list of related topics below.

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-type3
Specifies the data type of the method's result. In this case, exactly the same considerations apply as for the parameters of methods described above under 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 means the following:
  • 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
Indicates what type of SQL statements can be executed. Because the SQL statement supported is the RETURN statement, the distinction has to do with whether or not the expression is a subquery.
READS SQL DATA
Indicates that SQL statements that do not modify SQL data can be executed by the method (SQLSTATE 42985). Nicknames cannot be referenced in the SQL statement (SQLSTATE 42997).
CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed by the method (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 run-time 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 not adequately checked out can compromise the integrity of a DB2® database. DB2 databases take 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.

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.
NO SQL, CONTAINS SQL, READS SQL DATA
Indicates whether the method issues any SQL statements and, if so, what type.
NO SQL
Indicates that the method cannot execute any SQL statements (SQLSTATE 38001).
CONTAINS SQL
Indicates that SQL statements that neither read nor modify SQL data can be executed by the method (SQLSTATE 38004 or 42985). Statements that are not supported in any method return a different error (SQLSTATE 38003 or 42985).
READS SQL DATA
Indicates that some SQL statements that do not modify SQL data can be included in the method (SQLSTATE 38002 or 42985). Statements that are not supported in any method return a different error (SQLSTATE 38003 or 42985).
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.
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 above. 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 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.

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 DB2 will accept this specification.
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 DB2 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-hand 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

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 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
Related reference:
Basic predicate
CREATE TABLE statement
CREATE TYPE statement
SET PATH statement
Special registers
Supported SQL data types in C and C++ embedded SQL applications
Supported SQL data types in COBOL embedded SQL applications
Supported SQL data types in FORTRAN embedded SQL applications
Supported SQL data types in REXX embedded SQL applications