This section describes the major tasks involved in using a user-defined structured type as the type of a column. Before reading this section, you should be familiar with the material in Structured Types Overview.
Structured types can be used in the context of tables, views, or columns. When you create a structured type, you can encapsulate both user-defined type behavior and type attributes. To include behavior for a type, specify a method signature with the CREATE TYPE or ALTER TYPE statement. For more information on creating methods, see User-Defined Functions (UDFs) and Methods.
Figure 15 shows the type hierarchy used as an example in this section. The root type is Address_t, which has three subtypes, each with an additional attribute that reflects some aspect of how addresses are formed in that country.
Figure 15. Structured type hierarchy for Address_t type
|
CREATE TYPE Address_t AS
(street VARCHAR(30),
number CHAR(15),
city VARCHAR(30),
state VARCHAR(10))
MODE DB2SQL;
CREATE TYPE Germany_addr_t UNDER Address_t AS
(family_name VARCHAR(30))
MODE DB2SQL;
CREATE TYPE Brazil_addr_t UNDER Address_t AS
(neighborhood VARCHAR(30))
MODE DB2SQL;
CREATE TYPE US_addr_t UNDER Address_t AS
(zip CHAR(10))
MODE DB2SQL;
Unless you are concerned with how structured types are laid out in the data record, there is no additional syntax for creating tables with columns of structured types. For example, the following statement adds a column of Address_t type to a Customer_List untyped table:
ALTER TABLE Customer_List
ADD COLUMN Address Address_t;
Now instances of Address_t or any of the subtypes of Address_t can be stored in this table. For information on inserting structured types, see Inserting Rows that Contain Structured Type Values.
If you are concerned with how structured types are laid out in the data record, you can use the INLINE LENGTH clause in the CREATE TYPE statement to indicate the maximum size of an instance of a structured type column to store inline with the rest of the values in the row. For more information on the INLINE LENGTH clause, refer to the CREATE TYPE (Structured) statement in the SQL Reference.
A type can be created with a structured type attribute, or it can be altered (before it is used) to add or drop such an attribute. For example, the following CREATE TYPE statement contains an attribute of type Address_t:
CREATE TYPE Person_t AS
(Name VARCHAR(20),
Age INT,
Address Address_t)
REF USING VARCHAR(13)
MODE DB2SQL;
Person_t can be used as the type of a table, the type of a column in a regular table, or as an attribute of another structured type.
When you create a structured type, DB2 automatically generates a constructor method for the type, and generates mutator and observer methods for the attributes of the type. You can use these methods to create instances of structured types, and insert these instances into a column of a table.
Assume that you want to add a new row to the Employee typed table, and that you want that row to contain an address. Just as with built-in data types, you can add this row using INSERT with the VALUES clause. However, when you specify the value to insert into the address, you must invoke the system-provided constructor function to create the value:
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept, Address)
VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2),
US_addr_t ( ) (1)
..street('Bakely Avenue') (2)
..number('555') (3)
..city('San Jose') (4)
..state('CA') (5)
..zip('95141')); (6)
The previous statement creates an instance of the US_addr_t type by performing the following tasks:
Notice that although the type of the column Address in the Employee table is defined with type Address_t, the property of substitutability means that you can can populate it with an instance of US_addr_t because US_addr_t is a subtype of Address_t.
To avoid having to explicitly call the mutator methods for each attribute of a structured type every time you create an instance of the type, consider defining your own SQL-bodied constructor function that initializes all of the attributes. The following example contains the declaration for an SQL-bodied constructor function for the US_addr_t type:
CREATE FUNCTION US_addr_t
(street Varchar(30),
number Char(15),
city Varchar(30),
state Varchar(20),
zip Char(10))
RETURNS US_addr_t
LANGUAGE SQL
RETURN Address_t()..street(street)..number(number)
..city(city)..state(state)..zip(zip);
The following example demonstrates how to create an instance of the US_addr_t type by calling the SQL-bodied constructor function from the previous example:
INSERT INTO Employee(Oid, Name, Age, SerialNum, Salary, Dept, Address)
VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2),
US_addr_t('Bakely Avenue', '555', 'San Jose', 'CA', '95141'));
There are several ways that applications and user-defined functions can access data in structured type columns. If you want to treat an object as a single value, you must first define transform functions, which are described in Creating the Mapping to the Host Language Program: Transform Functions. Once you define the correct transform functions, you can select a structured object much as you can any other value:
SELECT Name, Dept, Address
FROM Employee
WHERE Salary > 20000;
In this section, however, we describe a way of explicitly accessing individual attributes of an object by invoking the DB2 built-in observer and mutator methods. The built-in methods do not require you to define a transform function.
To explicitly access individual attributes of an object, invoke the DB2 built-in observer methods on those attributes. Using the observer methods, you can retrieve the attributes individually rather than treating the object as a single value.
The following example accesses data in the Address column by invoking the observer methods on Address_t, the defined static type for the Address column:
SELECT Name, Dept, Address..street, Address..number, Address..city,
Address..state
FROM Employee
WHERE Salary > 20000;
| Note: | DB2 enables you to invoke methods that take no parameters using either <type-name>..<method-name>() or <type-name>..<method-name>, where type-name represents the name of the structured type, and attribute-name represents the name of the method that takes no parameters. |
You can also use observer methods to select each attribute into a host variable, as follows:
SELECT Name, Dept, Address..street, Address..number, Address..city,
Address..state
INTO :name, :dept, :street, :number, :city, :state
FROM Employee
WHERE Empno = '000250';
In the Employee table, addresses can be of 4 different types: Address_t, US_addr_t, Brazil_addr_t, and Germany_addr_t. The previous example accesses only the attributes of the static type Address_t. To access attributes of values from one of the subtypes of Address_t, you must use the TREAT expression to indicate to DB2 that a particular object can be of the US_addr_t, Germany_addr_t, or Brazil_addr_t types. The TREAT expression casts a structured type expression into one of its subtypes, as shown in the following query:
SELECT Name, Dept, Address..street, Address..number, Address..city,
Address..state,
CASE
WHEN Address IS OF (US_addr_t)
THEN TREAT(Address AS US_addr_t)..zip
WHEN Address IS OF (Germany_addr_t)
THEN TREAT (Address AS Germany_addr_t)..family_name
WHEN Address IS OF (Brazil_addr_t)
THEN TREAT (Address AS Brazil_addr_t)..neighborhood
ELSE NULL END
FROM Employee
WHERE Salary > 20000;
| Note: | You can only use the preceding approach to determine the subtype of a structured type when the attributes of the subtype are all of the same type, or can be cast to the same type. In the previous example, zip, family_name, and neighborhood are all VARCHAR or CHAR types, and can be cast to the same type. |
For more information about the syntax of the TREAT expression or the TYPE predicate, refer to the SQL Reference.
To change an attribute of a structured column value, invoke the mutator method for the attribute you want to change. For example, to change the street attribute of an address, you can invoke the mutator method for street with the value to which it will be changed. The returned value is an address with the new value for street. The following example invokes a mutator method for the attribute named street to update an address type in the Employee table:
UPDATE Employee
SET Address = Address..street('Bailey')
WHERE Address..street = 'Bakely';
The following example performs the same update as the previous example, but instead of naming the structured column for the update, the SET clause directly accesses the mutator method for the attribute named street:
UPDATE Employee
SET Address..street = 'Bailey'
WHERE Address..street = 'Bakely';
As described in Other Type-related Built-in Functions, you can use built-in functions to return the name, schema, or internal type ID of a particular type. The following statement returns the exact type of the address value associated with the employee named 'Iris':
SELECT TYPE_NAME(Address)
FROM Employee
WHERE Name='Iris';
Transform functions naturally occur in pairs: one FROM SQL transform function, and one TO SQL transform function. The FROM SQL function converts a structured type object into a type that can be exchanged with an external program, and the TO SQL function constructs the object. When you create transform functions, you put each logical pair of transform functions into a group. The transform group name uniquely identifies a pair of these functions for a given structured type.
Before you can use a transform function, you must use the CREATE TRANSFORM statement to associate the transform function with a group name and a type. The CREATE TRANSFORM statement identifies one or more existing functions and causes them to be used as transform functions. The following example names two pairs of functions to be used as transform functions for the type Address_t. The statement creates two transform groups, func_group and client_group, each of which consists of a FROM SQL transform and a TO SQL transform.
CREATE TRANSFORM FOR Address_t
func_group ( FROM SQL WITH FUNCTION addresstofunc,
TO SQL WITH FUNCTION functoaddress )
client_group ( FROM SQL WITH FUNCTION stream_to_client,
TO SQL WITH FUNCTION stream_from_client ) ;
You can associate additional functions with the Address_t type by adding more groups on the CREATE TRANSFORM statement. To alter the transform definition, you must reissue the CREATE TRANSFORM statement with the additional functions. For example, you might want to customize your client functions for different host language programs, such as having one for C and one for Java. To optimize the performance of your application, you might want your transforms to work only with a subset of the object attributes. Or you might want one transform that uses VARCHAR as the client representation for an object and one transform that uses BLOB.
Use the SQL statement DROP TRANSFORM to disassociate transform functions from types. After you execute the DROP TRANSFORM statement, the functions will still exist, but they will no longer be used as transform functions for this type. The following example disassociates the specific group of transform functions func_group for the Address_t type, and then disassociates all transform functions for the Address_t type:
DROP TRANSFORMS func_group FOR Address_t; DROP TRANSFORMS ALL FOR Address_t;
Transform group names are unqualified identifiers; that is, they are not associated with any specific schema. Unless you are writing transforms to handle subtype parameters, as described in Retrieving Subtype Data from DB2 (Bind Out), you should not assign a different transform group name for every structured type. Because you might need to use several different, unrelated types in the same program or in the same SQL statement, you should name your transform groups according to the tasks performed by the transform functions.
The names of your transform groups should generally reflect the function they perform without relying on type names or in any way reflecting the logic of the transform functions, which will likely be very different across the different types. For example, you could use the name func_group or object_functions for any group in which your TO and FROM SQL function transforms are defined. You could use the name client_group or program_group for a group that contains TO and FROM SQL client transforms.
In the following example, the Address_t and Polygon types use very different transforms, but they use the same function group names
CREATE TRANSFORM FOR Address_t
func_group (TO SQL WITH FUNCTION functoaddress,
FROM SQL WITH FUNCTION addresstofunc );
CREATE TRANSFORM FOR Polygon
func_group (TO SQL WITH FUNCTION functopolygon,
FROM SQL WITH FUNCTION polygontofunc);
Once you set the transform group to func_group in the appropriate situation, as described in Where Transform Groups Must Be Specified, DB2 invokes the correct transform function whenever you bind in or bind out an address or polygon.
| Restriction: | Do not begin a transform group with the string 'SYS'; this group is reserved for use by DB2. |
When you define an external function or method and you do not specify a transform group name, DB2 attempts to use the name DB2_FUNCTION, and assumes that that group name was specified for the given structured type. If you do not specify a group name when you precompile a client program that references a given structured type, DB2 attempts to use a group name called DB2_PROGRAM, and again assumes that the group name was defined for that type.
This default behavior is convenient in some cases, but in a more complex database schema, you might want a slightly more extensive convention for transform group names. For example, it may help you to use different group names for different languages to which you might bind out the type.
Considering that there can be many transform groups defined for a given structured type, you must specify which group of transforms to use for that type in a program or specific SQL statement. There are three circumstances in which you must specify transform groups:
The CREATE FUNCTION and CREATE METHOD statements enable you to specify the TRANSFORM GROUP clause, which is only valid when the value of the LANGUAGE clause is not SQL. SQL language functions do not require transforms, while external functions do require transforms. The TRANSFORM GROUP clause allows you to specify, for any given function or method, the transform group that contains the TO SQL and FROM SQL transforms used for structured type parameters and results. In the following example, the CREATE FUNCTION and CREATE METHOD statements specify the transform group func_group for the TO SQL and FROM SQL transforms:
CREATE FUNCTION stream_from_client (VARCHAR (150))
RETURNS Address_t
...
TRANSFORM GROUP func_group
EXTERNAL NAME 'addressudf!address_stream_from_client'
...
CREATE METHOD distance ( point )
FOR polygon
RETURNS integer
:
TRANSFORM GROUP func_group ;
If you use dynamic SQL, you can set the CURRENT DEFAULT TRANSFORM GROUP special register. This special register is not used for static SQL statements or for the exchange of parameters and results with external functions or methods. Use the SET CURRENT DEFAULT TRANSFORM GROUP statement to set the default transform group for your dynamic SQL statements:
SET CURRENT DEFAULT TRANSFORM GROUP = client_group;
For static SQL, use the TRANSFORM GROUP option on the PRECOMPILE or BIND command to specify the static transform group used by static SQL statements to exchange values of various types with host programs. Static transform groups do not apply to dynamic SQL statements, or to the exchange of parameters and results with external functions or methods. To specify the static transform group on the PRECOMPILE or BIND command, use the TRANSFORM GROUP clause:
PRECOMPILE ... TRANSFORM GROUP client_group ... ;
For more information on the PRECOMPILE and BIND commands, refer to the Command Reference.
An application cannot directly select an entire object, although, as described in Retrieving Attributes, you can select individual attributes of an object into an application. An application usually does not directly insert an entire object, although it can insert the result of an invocation of the constructor function:
INSERT INTO Employee(Address) VALUES (Address_t());
To exchange whole objects between the server and client applications, or external functions, you must normally write transform functions.
A transform function defines how DB2 converts an object into a well-defined format for accessing its contents, or binds out the object. A different transform function defines how DB2 returns the object to be stored in the database, or binds in the object. Transforms that bind out an object are called FROM SQL transform functions, and transforms that bind in a column object are called TO SQL transforms.
Most likely, there will be different transforms for passing objects to routines, or external UDFs and methods, than those for passing objects to client applications. This is because when you pass the object to an external routine, you decompose the object and pass it to the routine as a list of parameters. With client applications, you must turn the object into a single built-in type, such as a BLOB. This process is called encoding the object. Often these two types of transforms are used together.
Use the SQL statement CREATE TRANSFORM to associate transform functions with a particular structured type. Within the CREATE TRANSFORM statement, the functions are paired into what are called transform groups. This makes it easier to identify which functions are used for a particular transform purpose. Each transform group can contain not more than one FROM SQL transform, and not more than one TO SQL transform, for a particular type.
| Note: | The following topics cover the simple case in which the application always receives a known exact type, such as Address_t. These topics do not describe the likely scenario in which an external routine or a client program may receive Address_t, Brazil_addr_t, Germany_addr_t, or US_addr_t. However, you must understand the basic process before attempting to apply that basic process to the more complex case, in which the external routine or client needs to handle dynamically any type or its subtypes. For information about how to dynamically handle subtype instances, see Retrieving Subtype Data from DB2 (Bind Out). |
This section describes a particular type of transforms called function transforms. DB2 uses these TO SQL and FROM SQL function transforms to pass an object to and from an external routine. There is no need to use transforms for SQL-bodied routines. However, as Exchanging Objects with a Program: Client Transforms describes, DB2 often uses these functions as part of the process of passing an object to and from a client program.
The following example issues an SQL statement that invokes an external UDF called MYUDF that takes an address as an input parameter, modifies the address (to reflect a change in street names, for example), and returns the modified address:
SELECT MYUDF(Address) FROM PERSON;
Figure 16 shows how DB2 processes the address.
Figure 16. Exchanging a structured type parameter with an external routine
 |
| Note: | If MYUDF also returns a structured type, another transform
function must transform the resultant structured type when the UDF is used in
a SELECT clause. To avoid creating another transform function, you can
use SELECT statements with observer methods, as in the following
example:
SELECT Name
FROM Employee
WHERE MYUDF(Address)..city LIKE 'Tor%';
|
To decompose and construct objects when exchanging the object with an external routine, you can use user-defined functions written in SQL, called SQL-bodied routines. To create a SQL-bodied routine, issue a CREATE FUNCTION statement with the LANGUAGE SQL clause.
In your SQL-bodied function, you can use constructors, observers, and mutators to achieve the transformation. As shown in Figure 16, this SQL-bodied transform intervenes between the SQL statement and the external function. The FROM SQL transform takes the object as an SQL parameter and returns a row of values representing the attributes of the structured type. The following example contains a possible FROM SQL transform function for an address object using a SQL-bodied function:
CREATE FUNCTION addresstofunc (A Address_t) (1)
RETURNS ROW (Street VARCHAR(30), Number CHAR(15),
City VARCHAR(30), State (VARCHAR(10)) (2)
LANGUAGE SQL (3)
RETURN VALUES (A..Street, A..Number, A..City, A..State) (4)
The following list explains the syntax of the preceding CREATE FUNCTION statement:
DB2 does not know that you intend to use this function as a transform function. Until you create a transform group that uses this function, and then specify that transform group in the appropriate situation, DB2 cannot use the function as a transform function. For more information, see Associating Transforms with a Type.
The TO SQL transform simply does the opposite of the FROM SQL function. It takes as input the list of parameters from a routine and returns an instance of the structured type. To construct the object, the following FROM SQL function invokes the constructor function for the Address_t type:
CREATE FUNCTION functoaddress (street VARCHAR(30), number CHAR(15),
city VARCHAR(30), state VARCHAR(10)) (1)
RETURNS Address_t (2)
LANGUAGE SQL
CONTAINS SQL
RETURN
Address_t()..street(street)..number(number)
..city(city)..state(state) (3)
The following list explains the syntax of the previous statement:
The order of parameters in the FROM SQL function does not matter, other than that all functions that return addresses must use this same order.
When you pass structured type parameters to an external routine, you should pass a parameter for each attribute. You must pass a null indicator for each parameter and a null indicator for the structured type itself. The following example accepts the structured type Address_t and returns a base type:
CREATE FUNCTION stream_to_client (Address_t)
RETURNS VARCHAR(150) ...
The external routine must accept the null indicator for the instance of the Address_t type (address_ind) and one null indicator for each of the attributes of the Address_t type. There is also a null indicator for the VARCHAR output parameter. The following code represents the C language function headers for the functions which implement the UDFs:
void SQL_API_FN stream_to_client(
/*decomposed address*/
SQLUDF_VARCHAR *street,
SQLUDF_CHAR *number,
SQLUDF_VARCHAR *city,
SQLUDF_VARCHAR *state,
SQLUDF_VARCHAR *output,
/*null indicators for type attributes*/
SQLUDF_NULLIND *street_ind,
SQLUDF_NULLIND *number_ind,
SQLUDF_NULLIND *city_ind,
SQLUDF_NULLIND *state_ind,
/*null indicator for instance of the type*/
SQLUDF_NULLIND *address_ind,
/*null indicator for the VARCHAR output*/
SQLUDF_NULLIND *out_ind,
SQLUDF_TRAIL_ARGS)
Suppose that the routine accepts two different structured type parameters, st1 and st2, and returns another structured type of st3:
CREATE FUNCTION myudf (int, st1, st2)
RETURNS st3
Table 14. Attributes of myudf parameters
| ST1 | ST2 | ST3 |
|---|---|---|
| st1_att1 VARCHAR | st2_att1 VARCHAR | st3_att1 INTEGER |
| st2_att2 INTEGER | st2_att2 CHAR | st3_att2 CLOB |
|
| st2_att3 INTEGER |
|
The following code represents the C language headers for routines which implement the UDFs. The arguments include variables and null indicators for the attributes of the decomposed structured type and a null indicator for each instance of a structured type, as follows:
void SQL_API_FN myudf(
SQLUDF_INTEGER *INT,
/* Decompose st1 input */
SQLUDF_VARCHAR *st1_att1,
SQLUDF_INTEGER *st1_att2,
/* Decompose st2 input */
SQLUDF_VARCHAR *st2_att1,
SQLUDF_CHAR *st2_att2,
SQLUDF_INTEGER *st2_att3,
/* Decompose st3 output */
SQLUDF_VARCHAR *st3_att1out,
SQLUDF_CLOB *st3_att2out,
/* Null indicator of integer*/
SQLUDF_NULLIND *INT_ind,
/* Null indicators of st1 attributes and type*/
SQLUDF_NULLIND *st1_att1_ind,
SQLUDF_NULLIND *st1_att2_ind,
SQLUDF_NULLIND *st1_ind,
/* Null indicators of st2 attributes and type*/
SQLUDF_NULLIND *st2_att1_ind,
SQLUDF_NULLIND *st2_att2_ind,
SQLUDF_NULLIND *st2_att3_ind,
SQLUDF_NULLIND *st2_ind,
/* Null indicators of st3_out attributes and type*/
SQLUDF_NULLIND *st3_att1_ind,
SQLUDF_NULLIND *st3_att2_ind,
SQLUDF_NULLIND *st3_ind,
/* trailing arguments */
SQLUDF_TRAIL_ARGS
)
This section describes client transforms. Client transforms exchange structured types with client application programs.
For example, assume that you want to execute the following SQL statement:
...
SQL TYPE IS Address_t AS VARCHAR(150) addhv;
...
EXEC SQL SELECT Address
FROM Person
INTO :addhv
WHERE AGE > 25
END EXEC;
Figure 17 shows the process of binding out that address to the client program.
Figure 17. Binding out a structured type to a client application
 |
This encoding can be as simple as copying the attributes into a contiguous area of storage (providing for required alignments as necessary). Because the encoding and decoding of attributes cannot generally be achieved with SQL, client transforms are usually written as external UDFs.
For information about processing data between platforms, see Data Conversion Considerations.
Figure 18 shows the reverse process of passing the address back to the database.
Figure 18. Binding in a structured type from a client
 |
Register the client transforms the same way as any other external UDF. For example, assume that you have written external UDFs that do the appropriate encoding and decoding for an address. Suppose that you have named the FROM SQL client transform from_sql_to_client and the TO SQL client transform to_sql_from_client. In both of these cases, the output of the functions are in a format that can be used as input by the appropriate FROM SQL and TO SQL function transforms.
CREATE FUNCTION from_sql_to_client (Address_t)
RETURNS VARCHAR (150)
LANGUAGE C
TRANSFORM GROUP func_group
EXTERNAL NAME 'addressudf!address_from_sql_to_client'
NOT VARIANT
NO EXTERNAL ACTION
NOT FENCED
NO SQL
PARAMETER STYLE DB2SQL;
The DDL in the previous example makes it seem as if the from_sql_to_client UDF accepts a parameter of type Address_t. What really happens is that, for each row for which the from_sql_to_client UDF is invoked, the Addresstofunc transform decomposes the Address into its various attributes. The from_sql_to_client UDF produces a simple character string and formats the address attributes for display, allowing you to use the following simple SQL query to display the Name and Address attributes for each row of the Person table:
SELECT Name, from_sql_to_client (Address) FROM Person;
The following DDL registers a function that takes the VARCHAR-encoded object from the client, decomposes it into its various base type attributes, and passes it to the TO SQL function transform.
CREATE FUNCTION to_sql_from_client (VARCHAR (150))
RETURNS Address_t
LANGUAGE C
TRANSFORM GROUP func_group
EXTERNAL NAME 'addressudf!address_to_sql_from_client'
NOT VARIANT
NO EXTERNAL ACTION
NOT FENCED
NO SQL
PARAMETER STYLE DB2SQL;
Although it appears as if the to_sql_from_client returns the address directly, what really happens is that to_sql_from_client converts the VARCHAR (150) to a set of base type attributes. Then DB2 implicitly invokes the TO SQL transform functoaddress to construct the address object that is returned to the database.
How does DB2 know which function transform to invoke? Notice that the DDL in both to_sql_from_client and from_sql_to_client include a clause called TRANSFORM GROUP. This clause tells DB2 which set of transforms to use in processing the address type in those functions. For more information, see Associating Transforms with a Type.
It is important to note that when data, especially binary data, is exchanged between server and client, there are several data conversion issues to consider. For example, when data is transferred between platforms with different byte-ordering schemes, numeric data must undergo a byte-reversal process to restore its correct numeric value. Different operating systems also have certain alignment requirements for referencing numeric data in memory; some operating systems will cause program exceptions if these requirements are not observed. Character data types are automatically converted by the database, except when character data is embedded in a binary data type such as BLOB or a VARCHAR FOR BIT DATA.
There are two ways to avoid data conversion problems:
This approach has the disadvantages of slowing performance, due to the many potential conversions required, and increasing the complexity of code accessing these objects, such as on the client or in the transform function itself.
| Note: | As much as possible, you should write transform functions so that they correctly handle all of the complexities associated with the transfer of data between server and client. When you design your application, consider the specific requirements of your environment and evaluate the tradeoffs between complete generality and simplicity. For example, if you know that both the database server and all of its clients run in an AIX environment and use the same code page, you could decide to ignore the previously discussed considerations, because no conversions are currently required. However, if your environment changes in the future, you may have to exert considerable effort to revise your original design to correctly handle data conversion. |
Table 15 is intended to help you determine what transform functions
you need, depending on whether you are binding out to an external routine or a
client application.
Table 15. Characteristics of transform functions
| Characteristic | Exchanging values with an external routine | Exchanging values with a client application | ||
|---|---|---|---|---|
| Transform direction | FROM SQL | TO SQL | FROM SQL | TO SQL |
| What is being transformed | Routine parameter | Routine result | Output host variable | Input host variable |
| Behavior | Decomposes | Constructs | Encodes | Decodes |
| Transform function parameters | Structured type | Row of built-in types | Structured type | One built-in type |
| Transform function result | Row of built-in types (probably attributes) | Structured type | One built-in type | Structured type |
| Dependent on another transform? | No | No | FROM SQL UDF transform | TO SQL UDF transform |
| When is the transform group specified? | At the time the UDF is registered |
Static: precompile time Dynamic: Special register | ||
| Are there data conversion considerations? | No | Yes | ||
| Note: | Although not generally the case, client type transforms can actually be
written in SQL if any of the following are true:
|
Most of the information in the previous sections assume that the application is passing around a known exact type. If your data model takes advantage of subtypes, a value in a column could be one of many different subtypes. This section describes how you can dynamically choose the correct transform functions based on the actual input type.
Suppose you want to issue the following SELECT statement:
SELECT Address
FROM Person
INTO :hvaddr;
The application has no way of knowing whether a instance of Address_t, US_addr_t, or so on, will be returned. To keep the example from being too complex, let us assume that only Address_t or US_addr_t can be returned. The structures of these types are different, so the transforms that decompose the attributes must be different. To ensure that the proper transforms are invoked, perform the following steps:
Create a FROM SQL function transform for each variation of address: CREATE FUNCTION addresstofunc(A address_t)
RETURNS ROW
(Street VARCHAR(30), Number CHAR(15), City
VARCHAR(30), STATE VARCHAR (10))
LANGUAGE SQL
RETURN VALUES
(A..Street, A..Number, A..City, A..State)
CREATE FUNCTION US_addresstofunc(A US_addr_t)
RETURNS ROW
(Street VARCHAR(30), Number CHAR(15), City
VARCHAR(30), STATE VARCHAR (10), Zip
CHAR(10))
LANGUAGE SQL
RETURN VALUES
(A..Street, A..Number, A..City, A..State, A..Zip)
| |||
Create transform groups, one for each type variation: CREATE TRANSFORM FOR Address_t
funcgroup1 (FROM SQL WITH FUNCTION addresstofunc)
CREATE TRANSFORM FOR US_addr_t
funcgroup2 (FROM SQL WITH FUNCTION US_addresstofunc)
| |||
Create external UDFs, one for each type variation. Register the external UDF for the Address_t type: CREATE FUNCTION address_to_client (A Address_t)
RETURNS VARCHAR(150)
LANGUAGE C
EXTERNAL NAME 'addressudf!address_to_client'
...
TRANSFORM GROUP funcgroup1
Write the address_to_client UDF: void SQL_API_FN address_to_client(
SQLUDF_VARCHAR *street,
SQLUDF_CHAR *number,
SQLUDF_VARCHAR *city,
SQLUDF_VARCHAR *state,
SQLUDF_VARCHAR *output,
/* Null indicators for attributes */
SQLUDF_NULLIND *street_ind,
SQLUDF_NULLIND *number_ind,
SQLUDF_NULLIND *city_ind,
SQLUDF_NULLIND *state_ind,
/* Null indicator for instance */
SQLUDF_NULLIND *address_ind,
/* Null indicator for output */
SQLUDF_NULLIND *output_ind,
SQLUDF_TRAIL_ARGS)
{
sprintf (output, "[address_t] [Street:%s] [number:%s]
[city:%s] [state:%s]",
street, number, city, state);
*output_ind = 0;
}
Register the external UDF for the US_addr_t type: CREATE FUNCTION address_to_client (A US_addr_t)
RETURNS VARCHAR(150)
LANGUAGE C
EXTERNAL NAME 'addressudf!US_addr_to_client'
...
TRANSFORM GROUP funcgroup2
Write the US_addr_to_client UDF: void SQL_API_FN US_address_to_client(
SQLUDF_VARCHAR *street,
SQLUDF_CHAR *number,
SQLUDF_VARCHAR *city,
SQLUDF_VARCHAR *state,
SQLUDF_CHAR *zip,
SQLUDF_VARCHAR *output,
/* Null indicators */
SQLUDF_NULLIND *street_ind,
SQLUDF_NULLIND *number_ind,
SQLUDF_NULLIND *city_ind,
SQLUDF_NULLIND *state_ind,
SQLUDF_NULLIND *zip_ind,
SQLUDF_NULLIND *us_address_ind,
SQLUDF_NULLIND *output_ind,
SQLUDF_TRAIL_ARGS)
{
sprintf (output, "[US_addr_t] [Street:%s] [number:%s]
[city:%s] [state:%s] [zip:%s]",
street, number, city, state, zip);
*output_ind = 0;
}
| |||
Create a SQL-bodied UDF that chooses the correct external UDF to process the instance. The following UDF uses the TREAT specification in SELECT statements combined by a UNION ALL clause to invoke the correct FROM SQL client transform: CREATE FUNCTION addr_stream (ab Address_t)
RETURNS VARCHAR(150)
LANGUAGE SQL
RETURN
WITH temp(addr) AS
(SELECT address_to_client(ta.a)
FROM TABLE (VALUES (ab)) AS ta(a)
WHERE ta.a IS OF (ONLY Address_t)
UNION ALL
SELECT address_to_client(TREAT (tb.a AS US_addr_t))
FROM TABLE (VALUES (ab)) AS tb(a)
WHERE tb.a IS OF (ONLY US_addr_t))
SELECT addr FROM temp;
At this point, applications can invoke the appropriate external UDF by invoking the Addr_stream function: SELECT Addr_stream(Address)
FROM Employee;
| |||
Add the Addr_stream external UDF as a FROM SQL client transform for Address_t: CREATE TRANSFORM GROUP FOR Address_t
client_group (FROM SQL
WITH FUNCTION Addr_stream)
| |||
Bind the application with the TRANSFORM GROUP option set to client_group. PREP myprogram TRANSFORM GROUP client_group
|
When DB2 binds the application that contains the SELECT Address FROM Person INTO :hvar statement, DB2 looks for a FROM SQL client transform. DB2 recognizes that a structured type is being bound out, and looks in the transform group client_group because that is the TRANSFORM GROUP specified at bind time in 6.
The transform group contains the transform function Addr_stream associated with the root type Address_t in 5. Addr_stream is a SQL-bodied function, defined in 4, so it has no dependency on any other transform function. The Addr_stream function returns VARCHAR(150), the data type required by the :hvaddr host variable.
The Addr_stream function takes an input value of type Address_t, which can be substituted with US_addr_t in this example, and determines the dynamic type of the input value. When Addr_stream determines the dynamic type, it invokes the corresponding external UDF on the value: address_to_client if the dynamic type is Address_t; or USaddr_to_client if the dynamic type is US_addr_t. These two UDFs are defined in 3. Each UDF decomposes their respective structured type to VARCHAR(150), the type required by the Addr_stream transform function.
To accept the structured types as input, each UDF needs a FROM SQL transform function to decompose the input structured type instance into individual attribute parameters. The CREATE FUNCTION statements in 3 name the TRANSFORM GROUP that contains these transforms.
The CREATE FUNCTION statements for the transform functions are issued in 1. The CREATE TRANSFORM statements that associate the transform functions with their transform groups are issued in 2.
Once the application described in Retrieving Subtype Data from DB2 (Bind Out) manipulates the address value, it may need to insert the changed value back into the database. Suppose you want to insert a structured type into a DB2 database from an application using the following syntax:
INSERT INTO person (Oid, Name, Address)
VALUES ('n', 'Norm', :hvaddr);
To execute the INSERT statement for a structured type, your application must perform the following steps:
Create a TO SQL function transform for each variation of address. The following example shows SQL-bodied UDFs that transform the Address_t and US_addr_t types: CREATE FUNCTION functoaddress
(str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10))
RETURNS Address_t
LANGUAGE SQL
RETURN Address_t()..street(str)..number(num)..city(cy)..state(st);
CREATE FUNCTION functoaddress
(str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10),
zp CHAR(10))
RETURNS US_addr_t
LANGUAGE SQL
RETURN US_addr_t()..street(str)..number(num)..city(cy)
..state(st)..zip(zp);
| |
Create transform groups, one for each type variation: CREATE TRANSFORM FOR Address_t
funcgroup1 (TO SQL
WITH FUNCTION functoaddress);
CREATE TRANSFORM FOR US_addr_t
funcgroup2 (TO SQL
WITH FUNCTION functousaddr);
| |
Create external UDFs that return the encoded address types, one for each type variation. Register the external UDF for the Address_t type: CREATE FUNCTION client_to_address (encoding VARCHAR(150))
RETURNS Address_t
LANGUAGE C
TRANSFORM GROUP funcgroup1
...
EXTERNAL NAME 'address!client_to_address';
Write the external UDF for the Address_t version of client_to_address: void SQL_API_FN client_to_address (
SQLUDF_VARCHAR *encoding,
SQLUDF_VARCHAR *street,
SQLUDF_CHAR *number,
SQLUDF_VARCHAR *city,
SQLUDF_VARCHAR *state,
/* Null indicators */
SQLUDF_NULLIND *encoding_ind,
SQLUDF_NULLIND *street_ind,
SQLUDF_NULLIND *number_ind,
SQLUDF_NULLIND *city_ind,
SQLUDF_NULLIND *state_ind,
SQLUDF_NULLIND *address_ind,
SQLUDF_TRAIL_ARGS )
{
char c[150];
char *pc;
strcpy(c, encoding);
pc = strtok (c, ":]");
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (street, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (number, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (city, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (state, pc);
*street_ind = *number_ind = *city_ind
= *state_ind = *address_ind = 0;
}
Register the external UDF for the US_addr_t type: CREATE FUNCTION client_to_us_address (encoding VARCHAR(150))
RETURNS US_addr_t
LANGUAGE C
TRANSFORM GROUP funcgroup1
...
EXTERNAL NAME 'address!client_to_US_addr';
Write the external UDF for the US_addr_t version of client_to_address: void SQL_API_FN client_to_US_addr(
SQLUDF_VARCHAR *encoding,
SQLUDF_VARCHAR *street,
SQLUDF_CHAR *number,
SQLUDF_VARCHAR *city,
SQLUDF_VARCHAR *state,
SQLUDF_VARCHAR *zip,
/* Null indicators */
SQLUDF_NULLIND *encoding_ind,
SQLUDF_NULLIND *street_ind,
SQLUDF_NULLIND *number_ind,
SQLUDF_NULLIND *city_ind,
SQLUDF_NULLIND *state_ind,
SQLUDF_NULLIND *zip_ind,
SQLUDF_NULLIND *us_addr_ind,
SQLUDF_TRAIL_ARGS)
{
char c[150];
char *pc;
strcpy(c, encoding);
pc = strtok (c, ":]");
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (street, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strncpy (number, pc,14);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (city, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strcpy (state, pc);
pc = strtok (NULL, ":]");
pc = strtok (NULL, ":]");
strncpy (zip, pc, 9);
*street_ind = *number_ind = *city_ind
= *state_ind = *zip_ind = *us_addr_ind = 0;
}
| |
Create a SQL-bodied UDF that chooses the correct external UDF for processing that instance. The following UDF uses the TYPE predicate to invoke the correct to client transform. The results are placed in a temporary table: CREATE FUNCTION stream_address (ENCODING VARCHAR(150))
RETURNS Address_t
LANGUAGE SQL
RETURN
(CASE(SUBSTR(ENCODING,2,POSSTR(ENCODING,']')-2))
WHEN 'address_t'
THEN client_to_address(ENCODING)
WHEN 'us_addr_t'
THEN client_to_us_addr(ENCODING)
ELSE NULL
END);
| |
Add the stream_address UDF as a TO SQL client transform for Address_t: CREATE TRANSFORM FOR Address_t
client_group (TO SQL
WITH FUNCTION stream_address);
| |
Bind the application with the TRANSFORM GROUP option set to client_group. PREP myProgram2 TRANSFORM GROUP client_group
|
When the application containing the INSERT statement with a structured type is bound, DB2 looks for a TO SQL client transform. DB2 looks for the transform in the transform group client_group because that is the TRANSFORM GROUP specified at bind time in 6. DB2 finds the transform function it needs: stream_address, which is associated with the root type Address_t in 5.
stream_address is a SQL-bodied function, defined in 4, so it has no stated dependency on any additional transform function. For input parameters, stream_address accepts VARCHAR(150), which corresponds to the application host variable :hvaddr. stream_address returns a value that is both of the correct root type, Address_t, and of the correct dynamic type.
stream_address parses the VARCHAR(150) input parameter for a substring that names the dynamic type: in this case, either 'Address_t' or 'US_addr_t'. stream_address then invokes the corresponding external UDF to parse the VARCHAR(150) and returns an object of the specified type. There are two client_to_address() UDFs, one to return each possible type. These UDFs are defined in 3. Each UDF takes the input VARCHAR(150), and internally constructs the attributes of the appropriate structured type, thus returning the structured type.
To return the structured types, each UDF needs a TO SQL transform function to construct the output attribute values into an instance of the structured type. The CREATE FUNCTION statements in 3 name the TRANSFORM GROUP that contains the transforms.
The SQL-bodied transform functions from 1, and the associations with the transform groups from 2, are named in the CREATE FUNCTION statements of 3.
To retrieve or send structured type host variables in static SQL, you must provide an SQL declaration that indicates the built-in type used to represent the structured type. The format of the declaration is as follows:
EXEC SQL BEGIN DECLARE SECTION ;
SQL TYPE IS structured_type AS base_type host-variable-name ;
EXEC SQL END DECLARE SECTION;
For example, assume that the type Address_t is to be transformed to a varying-length character type when passed to the client application. Use the following declaration for the Address_t type host variable:
SQL TYPE IS Address_t AS VARCHAR(150) addrhv;
A DESCRIBE of a statement with a structured type variable causes DB2 to put a description of the result type of the FROM SQL transform function in the SQLTYPE field of the base SQLVAR of the SQLDA. However, if there is no FROM SQL transform function defined, either because no TRANSFORM GROUP was specified using the CURRENT DEFAULT TRANSFORM GROUP special register or because the named group does not have a FROM SQL transform function defined, DESCRIBE returns an error.
The actual name of the structured type is returned in SQLVAR2. For more information about the structure of the SQLDA, refer to the SQL Reference.