Categories: MSDN / DotNet / Java / Scripts / Linux / PHP Ask - La ask - La Answer

Programmers Tech >> Developer Tech >> Database

How to define UDT in IBM DB2 environment?

Hi,
I am new user to DB2.
How can i define the user distinct data type (UDT) in IBM DB2 environment?
Please give an example, thank you.
Regards,
Sherlyn

[182 byte] By [Sherlyn] at [2007-11-9 18:51:27]

«« Create Procedure
»» Stored Proc syntax

# 1 Re: How to define UDT in IBM DB2 environment?

Hi Sherlyn,

CREATE DISTINCT TYPE shoesize AS INTEGER WITH COMPARISONS;

CREATE TABLE shoes(name VARCHAR(10), size shoesize);

INSERT INTO shoes VALUES('Nike', shoesize(5));
SELECT * FROM shoes WHERE size < shoesize(6);
or
SELECT * FROM shoes WHERE INTEGER(size) < 6;

Cheers
Serge

Serge Rielau at 2007-11-12 0:03:03 >

# 2 Re: How to define UDT in IBM DB2 environment?

Thanks for your prompt, Serge!

Regards,
Sherlyn

"Serge Rielau" <srielau@ca.ibm.com> wrote:
>
>Hi Sherlyn,
>
>CREATE DISTINCT TYPE shoesize AS INTEGER WITH COMPARISONS;
>
>CREATE TABLE shoes(name VARCHAR(10), size shoesize);
>
>INSERT INTO shoes VALUES('Nike', shoesize(5));
>SELECT * FROM shoes WHERE size < shoesize(6);
>or
>SELECT * FROM shoes WHERE INTEGER(size) < 6;
>
>Cheers
>Serge
>

Sherlyn at 2007-11-12 0:04:07 >

# 3 Re: How to define UDT in IBM DB2 environment?

And how can I use a structured UDT to create a table? (and especially add entries into that table)

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, and I haven't figured out how to store data in that table.

genus.neduba at 2007-11-12 0:05:02 >