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