UDF table advice needed

[eviewcs]

Hello, I am relatively new to DB2 and i would appreciate if anybody
can give me advice on UDF table.

My question is :
1) Is performance an issue when using UDF table?

2) I read from previous post that SQL UDF work great when you can
reduce them to only RETURN. But if I have a function as followed will
i have performance issues? :

CREATE FUNCTION FUNC_TO_TBL(VAL 1 VARCHAR(128), VAL2 BIGINT, VAL3
CHAR(23))
RETURNS TABLE( TEST1 VARCHAR(128), TEST1 VARCHAR(128))
NO EXTERNAL ACTION
F1: BEGIN
DECLARE VARCHAR(128) FIELD1;
DECLARE VARCHAR(128) FIELD1;

// sql query + business logic to set value to Field1
SET FIELD1 = ....
// sql query + business logic to set value to Field2
SET FIELD2 = ....

RETURN VALUES(FIELD1 , FIELD2 );
END

3) How do I optimize the UDF table?

Any advice would be much appreciated!

[Janick Bernet]

Re: UDF table advice needed

Regarding the performance/one return only question: If you have only a
return in your UDF, the optimizer will merge the SQL into your
existing SQL code (just like it does with views) and perform it's
rewriting on it. So if you have only one return, there is no
performance impact in comparison to views or direct SQL.

I don't know how large the performance impact is, if you procedural
statements in your UDF, IMO it would depend on the queries you use,
and if and how the optimizer could rewrite and optimize them.

You can rewrite your function to contain only one return as follows:
--
CREATE FUNCTION FUNC_TO_TBL(VAL 1 VARCHAR(128), VAL2 BIGINT, VAL3
CHAR(23))
RETURNS TABLE( TEST1 VARCHAR(128), TEST1 VARCHAR(128))
NO EXTERNAL ACTION

RETURN
VALUES ((subquery business logic for field1) , (subquery business
logic for field2))
--

Regards,
Janick

[Knut Stolze]

Re: UDF table advice needed

eviewcs wrote:

Hello, I am relatively new to DB2 and i would appreciate if anybody
can give me advice on UDF table.
>
My question is :
1) Is performance an issue when using UDF table?

It depends...

2) I read from previous post that SQL UDF work great when you can
reduce them to only RETURN.

The DB2 optimizer is specifically tailored to optimize set-oriented SQL
statements. As soon as you introduce procedural logic, it just becomes
harder to rearrange operations because the optimizer may not know how to
break-down the procedural steps. Thus, procedural logic may slow down
things. (I'm using a lot of "may"s and "if"s because the real answer
depends you the specific situation/function/query.)

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany