Hi,

I need to use recursivity in DB2 functions and procedures.

Example:
CREATE FUNCTION fibonacci(
number integer
)
RETURNS integer
NO EXTERNAL ACTION NOT DETERMINISTIC READS SQL DATA
BEGIN ATOMIC
IF number = 0 OR number = 1 THEN
RETURN 1;
ELSE
RETURN fibonacci(number - 1) + fibonacci(number - 2);
END IF;
END;

Execution result:
SQLSTATE 42884
When a function or procedure calls other routines, the creation order must be ascending, from the most sheltered one to which makes first call. But in recursivity case it enters conflict, due to the called routine, which must exist, is the same one that contains the call.

How can recursivity be used in DB2 functions or procedures?

with tmp(next,prev,cnt) as (
values (1,0,0)
union all
select prev+next,next,cnt+1 from tmp
where cnt<10
) select * from tmp@


NEXT PREV CNT
----------- ----------- -----------
1 0 0
1 1 1
2 1 2
3 2 3
5 3 4
8 5 5
13 8 6
21 13 7
34 21 8
55 34 9
89 55 10

Thanks gardernman for that fibonacci series alternative solution, but my question is:

Does DB2 allow recusrivity or I should change my functions and procedures (algorithms) from recursivity to iterativity?

Anybody can answer me?