DB2 10.5 for Linux, UNIX, and Windows

CREATE TRANSFORM statement

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.

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:
  • Owner of the type identified by type-name, and EXECUTE privilege on every specified function
  • DBADM authority

Syntax

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

Notes:
  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

Notes

Example

Create two transform groups that associate the user-defined structured type polygon with transform functions customized for C and Java™, respectively.
   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)