Concept Question re UDTs in UDFs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rhino - 30 Mar 2005 16:13 GMT

I am trying to figure out if a UDF can accept a UDT (User-defined Distinct
Type, *not* structured type) as one of its parameters. I'm running DB2 for
Linux, Unix, and Windows V8.2 (FP 8) on Windows XP.

For instance, let's say I have two UDTs, ShoesizeEuropean and
ShoesizeNorthAmerican, both based on integers: can I write a UDF that
accepts a ShoesizeEuropean as its input parameter and returns a
ShoesizeNorthAmerican?

I'm finding the manuals very confusing on this point. The CREATE FUNCTION
(external scalar) article says this about the datatypes of the input
parameters:

"SQL data type specifications and abbreviations which may be specified
in the data-type1 definition of a CREATE TABLE statement and have a
correspondence in the language that is being used to write the function may
be specified."

This seems to imply that any datatype which you can put in a CREATE TABLE
statement is legal as an input parameter to a UDF provided it has a
corresponding datatype in the programming language being used for the UDF.
Therefore, if I want to have a ShoesizeEuropean distinct type in a Java UDF
it's okay as long as there is a corresponding Java class, presumably named
ShoesizeEuropean.

But I don't see how DB2 could be aware of a Java class named
ShoesizeEuropean when it executes a CREATE FUNCTION statement that looks
like this:

CREATE FUNCTION convert(ShoesizeEuropean inputSize)
returns ShoesizeNorthAmerican ...

Don't I need something that maps UDTs to their Java classes too? If so, how
do I do that mapping? CREATE TYPE MAPPING seemed like a possibility but it
is apparently for federated databases and I am using only DB2. CREATE
TRANSFORM seemed like a possibility, especially when I saw the TRANSFORM
GROUP clause in the CREATE FUNCTION (external scalar) statement, but the
article for CREATE TRANSFORM says it is only used for structured types, not
distinct types.

Can someone please explain the basic concepts to me so I know what I need to
do, assuming it is possible in the first place?

Signature

Rhino

Reply to this Message

Serge Rielau - 30 Mar 2005 17:11 GMT

> I am trying to figure out if a UDF can accept a UDT (User-defined Distinct
> Type, *not* structured type) as one of its parameters. I'm running DB2 for
[quoted text clipped - 38 lines]
> Can someone please explain the basic concepts to me so I know what I need to
> do, assuming it is possible in the first place?

For a UDT the implementation of the external UDT will simply use the
basetype (e.g. integer). As long as teh basetype exists you're cool.

When dealing with UDT you can also look at sourced functions and SQL
Functions.
If your american2europeansizes fucntion just is a fancy expression an
SQL Function should beat Java in performance hands down.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Rhino - 30 Mar 2005 17:42 GMT

> > I am trying to figure out if a UDF can accept a UDT (User-defined Distinct
> > Type, *not* structured type) as one of its parameters. I'm running DB2 for
[quoted text clipped - 41 lines]
> For a UDT the implementation of the external UDT will simply use the
> basetype (e.g. integer). As long as teh basetype exists you're cool.

So, since my ShoesizeEuropean is based on an Integer, there is no problem?
That's very useful to know. So, if I understand you correctly, my CREATE
FUNCTION needs to refer to integer rather than ShoesizeEuropean in the input
and output parameters. Is that correct?

Also, does the SQL statement that invokes the UDF need to convert the
distinct type to the base type manually or does this happen "automagically"?

For example, do I need this in my SQL (where convert() is the function that
changes European shoesizes to North American and euro_size is the name of
the column containing the European show size:

select id, name, convert(cast (euro_size as int)) ...

or will the cast happen "automagically" if I do this:

select id, name, convert(euro_size) ...

> When dealing with UDT you can also look at sourced functions and SQL
> Functions.
> If your american2europeansizes fucntion just is a fancy expression an
> SQL Function should beat Java in performance hands down.

That's also very useful to know. However, the Java UDFs will tend to be more
complex than a shoe size conversion; I just used shoe sizes as a simple
example ;-)

Rhino

Reply to this Message

Serge Rielau - 30 Mar 2005 20:07 GMT

>>For a UDT the implementation of the external UDT will simply use the
>>basetype (e.g. integer). As long as teh basetype exists you're cool.
[quoted text clipped - 3 lines]
> FUNCTION needs to refer to integer rather than ShoesizeEuropean in the input
> and output parameters. Is that correct?

The CREATE FUNCTION statement uses the UDT.
Your java implementation uses the base type.
UDT's are purely a game of the SQL Compiler frontend to enforce type
safety. The optimizer and runtime don't know they exist.

> Also, does the SQL statement that invokes the UDF need to convert the
> distinct type to the base type manually or does this happen "automagically"?

It's what we call a no-op cast. runtime doesn't waste a cycle. Just like
the C usage of a typedef does not change the executable.

> For example, do I need this in my SQL (where convert() is the function that
> changes European shoesizes to North American and euro_size is the name of
[quoted text clipped - 5 lines]
>
> select id, name, convert(euro_size) ...

Given that convert() was CREATEd to accept the UDT only teh later will
work. if you try the first DB2 will give you a function not found error.
Not sure how much epxerience you have with UDT, but make sure the schema
of the UDT is in your PATH. Otherwise DB2 will not find either casts or
comparisons.

>>When dealing with UDT you can also look at sourced functions and SQL
>>Functions.
[quoted text clipped - 4 lines]
> complex than a shoe size conversion; I just used shoe sizes as a simple
> example ;-)

And I was about to ask you for the conversion table I still confuse the
local retailers by telling them my German shoesize ;-)

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message