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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Genus Neduba - 16 Apr 2006 08:55 GMT

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.

Reply to this Message

Serge Rielau - 16 Apr 2006 15:02 GMT

> Hi,
>
[quoted text clipped - 10 lines]
> 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/developerworks/db2/library/techarticle/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.

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Genus Neduba - 17 Apr 2006 15:20 GMT

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

Reply to this Message

Serge Rielau - 17 Apr 2006 15:57 GMT

> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Genus Neduba - 18 Apr 2006 07:23 GMT

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

Reply to this Message

Knut Stolze - 18 Apr 2006 09:52 GMT

> Okay, here the complete statement (but it's in german)
>
[quoted text clipped - 13 lines]
> 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/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/op
t/rsbp4165.htm

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Genus Neduba - 18 Apr 2006 12:42 GMT

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

Reply to this Message

Knut Stolze - 18 Apr 2006 16:26 GMT

> 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.
[quoted text clipped - 7 lines]
> SQL20015N Eine Umsetzungsgruppe "DB2_PROGRAM" ist fr 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.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message