SQL Reference

CREATE TRANSFORM

The CREATE TRANSFORM statement defines transformation functions, identified by a group name, that are used to exchange structured type values with host language programs and with external functions and methods.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

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

SYSADM or DBADM authority
definer of the type identified by type-name and definer of every function specified.

Syntax

>>-CREATE--+-TRANSFORM--+---FOR--type-name---------------------->
           '-TRANSFORMS-'
 
      .------------------------------------------------------------------------------------.
      |                 .-,--------------------------------------------------------.       |
      V                 V                                                   (1)    |       |
>--------group-name--(------+-TO SQL---+---WITH--| function-specification |--------+---)---+>
                            '-FROM SQL-'
 
>--------------------------------------------------------------><
 
function-specification
 
|---+-FUNCTION--function-name--+-------------------------------+-+->
    |                          '-(--+-------------------+---)--' |
    |                               |  .-,-----------.  |        |
    |                               |  V             |  |        |
    |                               '----data-type---+--'        |
    '-SPECIFIC FUNCTION--specific-name---------------------------'
 
>---------------------------------------------------------------|

Notes:

The same clause must not be specified more than once.

Description

TRANSFORM or TRANSFORMS

Indicates that one or more transform groups is being defined. Either version of the keyword can be specified.

FOR type-name

Specifies a name for the user-defined structured type for which the transform group is being defined.

In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified type-name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for an unqualified type-name. The type-name must be the name of an existing user-defined type (SQLSTATE 42704), and it must be a structured type (SQLSTATE 42809). The structured type or any other structured type in the same type hierarchy must not have transforms already defined with the given group-name (SQLSTATE 42739).

group-name

Specifies the name of the transform group containing the TO SQL and FROM SQL functions. The name does not need to be unique; but a transform group of this name (with the same TO SQL and/or FROM SQL direction defined) must not be previously defined for the specified type-name (SQLSTATE 42739). A group-name must be an SQL identifier, with a maximum of 18 characters in length (SQLSTATE 42622), and it may not include any qualifier prefix (SQLSTATE 42601). The group-name cannot begin with the prefix 'SYS', since this is reserved for database use (SQLSTATE 42939).

At most, one of each of the FROM SQL and TO SQL function designations may be specified for any given group (SQLSTATE 42628).

TO SQL

Defines the specific function used to transform a value to the SQL user-defined structured type format. The function must have all its parameters as built-in data types and the returned type is type-name.

FROM SQL

Defines the specific function used to transform a value to a built in data type value representing the SQL user-defined structured type. The function must have one parameter of data type type-name, and return a built-in data type (or set of built-in data types).

WITH function-specification

There are several ways available to specify the function instance.

If FROM SQL is specified, function-specification must identify a function that meets the following requirements:

there is one parameter of type type-name
the return type is a built-in type, or a row whose columns all have built-in types
the signature specifies either LANGUAGE SQL or the use of another FROM SQL transform function which has LANGUAGE SQL.

If TO SQL is specified, function-specification must identify a function that meets the following requirements:

all parameters have built-in types
the return type is type-name
the signature specifies either LANGUAGE SQL or the use of another TO SQL transform function which has LANGUAGE SQL.

Methods (even if specified with FUNCTION ACCESS) cannot be specified as transforms through function-specification. Instead, only functions that are defined by the CREATE FUNCTION statement can act as transforms (SQLSTATE 42704 or 42883).

Additionally, although not enforced, the one or more built-in types which are returned from the FROM SQL function should directly correspond to the one or more built-in types which are parameters of the TO SQL function. This is a logical consequence of the inverse relationship between these two functions.

FUNCTION function-name

Identifies the particular function by name, and is valid only if there is exactly one function with the function-name. The function identified may have any number of parameters defined for it.

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/bind option implicitly specifies the qualifier for unqualified object names.

If no function by this name exists in the named or implied schema, an error is raised (SQLSTATE 42704). If there is more than one specific instance of the function in the named or implied schema, an error is raised (SQLSTATE 42725). The standard function selection algorithm is not used.

FUNCTION function-name (data-tape,...)

Provides the function signature, which uniquely identifies the function to be used. The standard function selection algorithm is not used.

function-name

Specifies the name of the function. 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/bind option implicitly specifies the qualifier for unqualified object names.

(data-type,...)

The data-types specified here must match the data types specified in the CREATE FUNCTION statement in the corresponding position. Both the number of data types and the logical concatenation of the data types are used to identify the specific function.

If the data-type is unqualified, the type name is resolved by searching the schemas on the SQL path. This also applies to data type names specified for a REFERENCE type.

It is not necessary to specify the length, precision or scale for the parameterized data types. Instead an empty set of parentheses can be coded to indicate that these attributes should be ignored when looking for a data type match.

FLOAT() cannot be used (SQLSTATE 42601), since the parameter value indicates different data types (REAL or DOUBLE). However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement.

A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE. Note that the FOR BIT DATA attribute is not considered part of the signature for matching purposes. For example, a CHAR FOR BIT DATA specified in the signature would match a function defined with CHAR only.

If no function with the specified signature exists in the named or implied schema, an error is raised (SQLSTATE 42883).

SPECIFIC FUNCTION specific-name

Identifies the particular user-defined function, using a specific name either specified or defaulted to at function creation time.

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/bind option implicitly specifies the qualifier for unqualified object names. The specific-name must identify a specific function instance in the named or implied schema; otherwise, an error is raised (SQLSTATE 42704).

Notes

When a transform group is not specified in an application program (using the TRANSFORM GROUP precompile or bind option for static SQL, or the SET CURRENT DEFAULT TRANSFORM GROUP statement for dynamic SQL), the transform functions in the transform group 'DB2_PROGRAM' are used (if defined) when the application program is retrieving or sending host variables that are based on the user-defined structured type identified by type-name. When retrieving a value of data type type-name, the FROM SQL transform is executed to transform the structured type to the built-in data type returned by the transform function. Similarly, when sending a host variable that will be assigned to a value of data type type-name, the TO SQL transform is executed to transform the built-in data type value to the structured type value. If a user-defined transform group is not specified or a 'DB2_PROGRAM' group is not defined (for the given structured type), an error results.
The built-in data type representation for a structured type host variable must be assignable:
- from the result of the FROM SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command (using retrieval assignment rules) and
- to the parameter of the TO SQL transform function for the structured type as defined by the specified TRANSFORM GROUP option of the precompile command (using storage assignment rules).
If a host variable is not assignment compatible with the type required by the applicable transform function, an error is raised (for bind-in: SQLSTATE 42821, for bind-out: SQLSTATE 42806). For errors that result from string assignments, see String Assignments.
The transform functions identified in the default transform group named 'DB2_FUNCTION' are used whenever a user-defined function not written in SQL is invoked using the data type type-name as a parameter or returns type. This applies when the function or method does not specify the TRANSFORM GROUP clause. When invoking the function with an argument of data type type-name, the FROM SQL transform is executed to transform the structured type to the built-in data type returned by the transform function. Similarly, when the returns data type of the function is of data type type-name, the TO SQL transform is executed to transform the built-in data type value returned from the external function program into the structured type value.
If a structured type contains an attribute which is also a structured type, the associated transform functions must recursively expand (or assemble) all nested structured types. This means that the results or parameters of the transform functions consist only of the set of built-in types representing all base attributes of the subject structured type (including all its nested structured types). There is no "cascading" of transform functions for handling nested structured types.
The function (or functions) identified in this statement are resolved according to the rules outlined above at the execution of this statement. When these functions are used (implicitly) in subsequent SQL statements, they do not undergo another resolution process. The transform functions defined in this statement are recorded exactly as they are resolved in this statement.
When attributes or subtypes of a given type are created or dropped, the transform functions for the user-defined structured type must also be changed.
For a given transform group, the FROM SQL and TO SQL functions can be specified in either the same group-name clause, in separate group-name clauses, or in separate CREATE TRANSFORM statements. The only restriction is that a given FROM SQL or TO SQL function designation may not be redefined without first dropping the existing group definition. This allows you to define, for example, a FROM SQL transform function for a given group first, and the corresponding TO SQL transform function for the same group at a later time.

Examples

Example 1: Create two transform groups that associate the user-defined structured type polygon with a transform function customized for C and one specialized for Java.

   CREATE TRANSFORM FOR POLYGON
      mystruct1 (FROM SQL WITH FUNCTION myxform_sqlstruct,
                 TO SQL WITH FUNCTION myxform_structsql)
      myjava1   (FROM SQL WITH FUNCTION myxform_sqljava,
                 TO SQL WITH FUNCTION myxform_javasql )

[ Top of Page | Previous Page | Next Page ]