How to use nested structured types (UDT's) in DB2?

I'm trying to used nested structured types (UDT's) using DB2 but have encountered some problems.

Below are the SQL statements for creating the types, table, functions and transforms for the use-case. Every statement executes fine, but an error occures when trying to do a simple select * from t_author:

CREATE TYPE u_street_type AS (
  st开发者_如何学编程reet VARCHAR(100),
  no VARCHAR(30)
) INSTANTIABLE MODE DB2SQL;

CREATE TYPE u_address_type AS (
  street u_street_type,
  zip VARCHAR(50),
  city VARCHAR(50),
  country VARCHAR(50),
  since DATE,
  code INT
) INSTANTIABLE MODE DB2SQL;

CREATE TABLE t_author (
  ID INT NOT NULL PRIMARY KEY,
  FIRST_NAME VARCHAR(50),
  LAST_NAME VARCHAR(50) NOT NULL,
  DATE_OF_BIRTH DATE NOT NULL,
  YEAR_OF_BIRTH INT,
  ADDRESS u_address_type
);

CREATE FUNCTION f_u_street_type_transform (street u_street_type) 
  RETURNS ROW (
    street VARCHAR(100), 
    no VARCHAR(30)
  )
  LANGUAGE SQL
  RETURN VALUES (
    street..street, 
    street..no
  );

CREATE TRANSFORM FOR u_street_type db2_program 
  (FROM SQL WITH FUNCTION f_u_street_type_transform);

CREATE FUNCTION f_u_address_type_transform (address u_address_type)
  RETURNS ROW (
    street VARCHAR(100),
    no VARCHAR(30),
    zip VARCHAR(50),
    city VARCHAR(50),
    country VARCHAR(50),
    since DATE,
    code INT
  )
  LANGUAGE SQL
  CONTAINS SQL
  NO EXTERNAL ACTION
  DETERMINISTIC
  RETURN VALUES (
    address..street..street,
    address..street..no,
    address..zip,
    address..city,
    address..country,
    address..since,
    address..code
  );

CREATE TRANSFORM FOR u_address_type db2_program 
  (FROM SQL WITH FUNCTION f_u_address_type_transform);

The following error occurs when I try to do a select * from t_author;:

The function "F_U_ADDRESS_TYPE_TRANSFORM" resolved to specific function 
"SQL101230131003100" that is not valid in the context where it is used.. 
SQLCODE=-390, SQLSTATE=42887, DRIVER=3.57.82

Any ideas what I'm doing wrong?

I'm using DB2 v9.5 (Linux).

---

The thing is that each value in column ADDRESS is a scalar value. When you want to bind-out values of structured types to a client application (like the DB2 CLP) because you have a query like "SELECT * FROM t_author", you have to use a scalar transform function, which converts the structured value into a single value of type VARCHAR, CLOB or whatever you need. You cannot expand it through the transform function into multiple values because that would have to be something like multiple columns. (And that's not possible because a different transform function could return a different number of values, giving a completely different schema for the query; not to mention the problems if you would use exactly the same query as a subquery with yet another semantics.)

Transform functions returning a ROW() with more than 1 column can only be used when exchanging values of structured types with external UDFs (written in C/C++ or Java).

p.s: My recommendation would be that you use regular, normalized relational database design and try to avoid structured types unless you have a really good reason to do so.

---

From DB2's perspective, jOOQ is just a database application. Therefore, all considerations for database applications are relevant for it as well. That means, jOOQ also needs to use a transform group with scalar functions as transform functions. There is no built-in support I am aware of.

What you could do is:

• create an external function that takes the separate parameters an input and concatenates them into a BLOB; jOOQ would then take the BLOB apart and construct the respective Java object
• convert the structured type to an XML document and jOOQ would parse the XML document and construct the Java object

In short: you will have to take care of retaining the structural information in the value transferred between DB2 and the application yourself. And your application has to take care of interpreting the data accordingly itself as well. So it's all a manual task for you like generate the type-specific code somehow.

Unfortunately, OO features in relational database systems in general have not progressed to the point where they are really easy and seamlessly to use. Because it is not easily usable, only very few applications use structured types. And because there are not a lot of users, improvements in that area won't get a high priority.

---

Thanks for the informative answer Knut!

I understand that I have to use a scalar transform function which converts the structured value into single value when using a client application (i.e. select * from t_author). I have managed this by creating a transform function that concatenates the structured value elements into a single varchar value.

This solves the problem with retrieving data from a client application, but the real reason for getting this to work is while trying to support structured types in the database interfacing libary jOOQ. With this library tables, columns, stored procedures, functions, structured types etc. are modelled as generated Java classes.

I was thinkink of using java.sql.Struct to retrieve data from the structured value column in the java.sql.ResultSet to populate the generated Java classes with values.

I managed to retrieve data using java.sql.Struct when I had the transform function the transformed the structured type into a varchar value but this is not what I want. I want "direct" access to the individual elements of the structured type.

Any tips on how to accomplish this?