Create a table with a structured UDT (user-defined type) as a column

[Genus Neduba]

Hi,

is it actually possible to create a table that has a UDT as a column
datatype?

e.g.:
CREATE TYPE addressType AS (
street INTEGER,
zip VARCHAR(30)) MODE DB2SQL

CREATE TABLE person (name VARCHAR(30), address addressType)

This doesn't seem to work. The statement SELECT * FROM person gives errors
(SQLSTATE=42741), and I haven't figured out how to store data in that table.

[Serge Rielau]

Genus Neduba wrote:

Hi,

is it actually possible to create a table that has a UDT as a column
datatype?

e.g.:
CREATE TYPE addressType AS (
street INTEGER,
zip VARCHAR(30)) MODE DB2SQL

CREATE TABLE person (name VARCHAR(30), address addressType)

This doesn't seem to work. The statement SELECT * FROM person gives errors
(SQLSTATE=42741), and I haven't figured out how to store data in that table.

db2 => ? 42741;
SQLSTATE 42741: A transform group is not defined for a data type.

So.. have you read up on what a "transform group" is? ;-)

Here is a quick intro:
http://www-128.ibm.com/developerwork...dm-0506melnyk/

Cheers
Serge

PS: Who is running the IM department in Muenster? I know that Prof Heuer
is OO, but he's in Rostock.

PPS: Knut Stolze is The Man for structured types.. and in your timezone,
too.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Genus Neduba]

Okay, so I have to create a function that transforms the structured type.

But when I try to create that function and use the '||' operator I get the
error message SQLSTATE=42884

[Serge Rielau]

Genus Neduba wrote:

Okay, so I have to create a function that transforms the structured type.

But when I try to create that function and use the '||' operator I get the
error message SQLSTATE=42884

db2 => ? 42884;

SQLSTATE 42884: No routine was found with the specified name and compatible
arguments.
(Keep in mind that we may be geeks here, but at least I don't think in
SQLSTATE numbers ;-)

I'm taking a guess here that you tried to concatenate a number. DB2 is
using strong typing.
If you want to || a number you have to turn the number into a string first:
'HELLO' || CHAR(5)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Genus Neduba]

Okay, here the complete statement (but it's in german)

Your SQL-statement: CREATE FUNCTION loc_transform (location_t) RETURNS
VARCHAR(60) LANGUAGE SQL RETURN 'building ' || loc..building || ', floor '
|| loc..floor || ', room ' || loc..room || ', booth ' || loc..booth

Fehler: SQL-Abfrage nicht durchführbar. [IBM][CLI Driver][DB2/LINUX]
SQL0440N Es wurde keine berechtigte Routine "||" des Typs "FUNCTION" mit
kompatiblen Argumenten gefunden. LINE NUMBER=2. SQLSTATE=42884

and:
CREATE TYPE location_t AS (
building INTEGER,
floor INTEGER,
room INTEGER,
booth VARCHAR(30)) MODE DB2SQL

[Knut Stolze]

Genus Neduba wrote:

Okay, here the complete statement (but it's in german)

Your SQL-statement: CREATE FUNCTION loc_transform (location_t)
RETURNS
VARCHAR(60) LANGUAGE SQL RETURN 'building ' || loc..building || ', floor '
|| loc..floor || ', room ' || loc..room || ', booth ' || loc..booth

Fehler: SQL-Abfrage nicht durchführbar. [IBM][CLI Driver][DB2/LINUX]
SQL0440N Es wurde keine berechtigte Routine "||" des Typs "FUNCTION" mit
kompatiblen Argumenten gefunden. LINE NUMBER=2. SQLSTATE=42884

and:
CREATE TYPE location_t AS (
building INTEGER,
floor INTEGER,
room INTEGER,
booth VARCHAR(30)) MODE DB2SQL

Note that VARCHAR(60) is too short if the numbers are maxed out. You don't
want to run into a run-time problem then!!

CREATE FUNCTION loc_transform(loc location_t)
RETURNS VARCHAR(100)
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
RETURN 'building ' || RTRIM(CHAR(loc..building)) ||
', floor ' || RTRIM(CHAR(loc..floor)) ||
', room ' || RTRIM(CHAR(loc..room)) ||
', booth ' || loc..booth

CREATE TRANSFORM FOR location_t my_transform (
FROM SQL WITH FUNCTION loc_transform )

CREATE TABLE t ( a int, b location_t )
INSERT INTO t VALUES (1, location_t()..building(1)..
floor(2)..room(3)..booth('booth'))
SET CURRENT DEFAULT TRANSFORM GROUP = my_transform

SELECT * FROM t

A B
----------- ---------------------------------------------------
1 building 1, floor 2, room 3, booth booth

1 record(s) selected.

Note that "TO SQL" transform functions/methods are a bit more complicated to
use. Have a look here for some examples:
http://publib.boulder.ibm.com/infoce...t/rsbp4165.htm

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Genus Neduba]

Well, thanks, I guess this should work, but it doesn't. It's probably due to
the restrictions (restricted privileges for me) on our maschine.

But I got the idea. At least I can store and read from that table with
structured-UDTs as Columns (even if I can't display it).
Your SQL-statement: SELECT * FROM t

Fehler: SQL-Abfrage nicht durchführbar. [IBM][CLI Driver][DB2/LINUX]
SQL20015N Eine Umsetzungsgruppe "DB2_PROGRAM" ist für den Datentyp
"LOCATION_T" nicht definiert. SQLSTATE=42741

[Knut Stolze]

Genus Neduba wrote:

Well, thanks, I guess this should work, but it doesn't. It's probably due
to the restrictions (restricted privileges for me) on our maschine.

But I got the idea. At least I can store and read from that table with
structured-UDTs as Columns (even if I can't display it).
Your SQL-statement: SELECT * FROM t

Fehler: SQL-Abfrage nicht durchführbar. [IBM][CLI Driver][DB2/LINUX]
SQL20015N Eine Umsetzungsgruppe "DB2_PROGRAM" ist für den Datentyp
"LOCATION_T" nicht definiert. SQLSTATE=42741

If you have no current default transform group specified, DB2 falls back to
the "db2_program" transform group. The transform group that I created was
named "my_transform" and I used the explicit statement:

SET CURRENT DEFAULT TRANSFORM GROUP = my_transform

Note that the setting is only active in the current SQL session.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany