This section contains information about the function transforms
used to pass an object to and from an external routine.
Db2® uses 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, 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 1 shows how Db2 processes the
address. Figure 1. Exchanging a structured type parameter with an external routine
Your FROM SQL transform function decomposes the structured object
into an ordered set of its base attributes. This enables the routine
to receive the object as a simple list of parameters whose types are
basic built-in data types. For example, assume that you want to pass
an address object to an external routine. The attributes of Address_t are
VARCHAR, CHAR, VARCHAR, and VARCHAR, in that order. The FROM SQL transform
for passing this object to a routine must accept this object as an
input and return VARCHAR, CHAR, VARCHAR, and VARCHAR. These outputs
are then passed to the external routine as four separate parameters,
with four corresponding null indicator parameters, and a null indicator
for the structured type itself. The order of parameters in the FROM
SQL function does not matter, provided that all functions that return Address_t types
use the same order.
Your external routine accepts the decomposed address as its input
parameters, does its processing on those values, and then returns
the attributes as output parameters.
Your TO SQL transform function must turn the VARCHAR, CHAR, VARCHAR,
and VARCHAR parameters that are returned from MYUDF back
into an object of type Address_t. In other words,
the TO SQL transform function must take the four parameters, and all
of the corresponding null indicator parameters, as output values from
the routine. The TO SQL function constructs the structured object
and then mutates the attributes with the given values.
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;
