Function transforms

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
Exchanging a structured type parameter with an external routine
  1. 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.
  2. 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.
  3. 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;