CREATE TRANSFORM statement

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

Syntax

>>-CREATE--+-TRANSFORM--+--FOR--type-name----------------------->
           '-TRANSFORMS-'                   

   .-----------------------------------------------------------------------.   
   |                .-,-----------------------------------------------.    |   
   V                V                                             (1) |    |   
>----group-name--(----+-TO SQL---+--WITH--| function-designator |-----+--)-+-><
                      '-FROM SQL-'                                             

function-designator

|--+-FUNCTION--function-name--+-------------------------+-+-----|
   |                          '-(--+---------------+--)-' |   
   |                               | .-,---------. |      |   
   |                               | V           | |      |   
   |                               '---data-type-+-'      |   
   '-SPECIFIC FUNCTION--specific-name---------------------'   

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

Names the transform group. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The group-name must not identify a transform group that already exists in the catalog for the specified type-name (SQLSTATE 42739). The group-name must not begin with the characters 'SYS' (SQLSTATE 42939). At most, one of each of the FROM SQL and TO SQL function designations can 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-designator

Uniquely identifies the transform function.

If FROM SQL is specified, function-designator 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 that has LANGUAGE SQL.

If TO SQL is specified, function-designator 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 that has LANGUAGE SQL.

If function-designator identifies a function that does not meet these requirements (according to its use as a FROM SQL or a TO SQL transform function), an error is raised (SQLSTATE 428DC).

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

For more information, see Function, method, and procedure designators.

Rules

• The one or more built-in types that are returned from the FROM SQL function should directly correspond to the one or more built-in types that are parameters of the TO SQL function. This is a logical consequence of the inverse relationship between these two functions.

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 invoked 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 invoked 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 is raised (SQLSTATE 42741).
• 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 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 invoked 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 that 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 functions identified in this statement are resolved according to the rules outlined previously 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 transforms 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 transform designation may not be redefined without first dropping the existing group definition. This allows you to define, for example, a FROM SQL transform for a given group first, and the corresponding TO SQL transform for the same group at a later time.

Example

   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)