> Hello
>
> Is it possible to write a recursive (a function which calls itself) UDF
> in DB2 (v.9) ?
>
> When I load one, I get SQL0440N - function "XXX" not found...
You have to go through dynamic SQL. To do that in a UDF you need a
helper procedure.
Here is a recipe (untested):
--#SET TERMINATOR @
CREATE PROCEDURE poo(IN a INT, OUT aplus1 INT)
BEGIN
DECLARE txt VARCHAR(200);
DECLARE stmt STATEMENT;
DECLARE cur CURSOR FOR stmt;
SET aplus1 = a;
IF a < 60 THEN
SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
PREPARE stmt FROM txt;
OPEN cur;
FETCH cur INTO aplus1;
CLOSE cur1;
END IF;
END
@
CREATE FUNCTION FOO(a INT) RETURNS INT
BEGIN ATOMIC
DECLARE aplus1 INT;
CALL POO(a, aplus1);
RETURN aplus1;
END
@
--#SET TERMINATOR ;
VALUES FOO(1);
Note that the permitted level of recursion is 64 in DB2 9 (up from 16 in
DB2 V8)
Cheers
Serge

Signature
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Serge Rielau - 28 May 2007 13:47 GMT
> SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
Note: In your production solution you want to use parameter markers for
the function arguments of course. I was a bit sloppy here.

Signature
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Szymon Dembek - 28 May 2007 13:51 GMT
>> SET txt = 'SELECT FOO('|| CHAR(a) || ') + 1 FROM SYSIBM.SYSDUMMY1';
>
> Note: In your production solution you want to use parameter markers for
> the function arguments of course. I was a bit sloppy here.
Thanks a lot !

Signature
Szymon Dembek
>> Is it possible to write a recursive (a function which calls itself) UDF in DB2 (v.9) ? <<
Perhaps if you post your actual problem, we can find a way to avoid
recursion.