1. Registered User
Join Date
Feb 2005
Location
Barcelona
Posts
42

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?
Last edited by dvtdbd; 02-02-05 at 07:38.

2. Registered User
Join Date
Apr 2004
Posts
54
Originally Posted by dvtdbd

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

3. Registered User
Join Date
Feb 2005
Location
Barcelona
Posts
42

## Is recursivity possible?

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?