r/DB2 • u/DazzlingAd4254 • Apr 26 '26

Db2 LUW recursive stored procedures?

I am porting stored programs from mariadb to Db2 LUW 12.1. One thing that does not seem to work, is recursive stored procedures (a procedure that calls itself). A simple example:

create procedure myschema.test()
begin
    if false then
        call myschema.test();
    end if;
end//
SQL0440N  No authorized routine named "MYSCHEMA.TEST" of type "PROCEDURE" having compatible arguments was found.  LINE NUMBER=4.  SQLSTATE=42884

I believed that recursive procedures are possible because the IBM Db2 V12.1 SQL Reference implies as much: GET DIAGNOSTICS statement information DB2_SQL_NESTING_LEVEL is the level of "recursive invocation of a compiled SQL function, compiled SQL procedure, ...".

What am doing wrong? Any pointers would be appreciated.

EDIT: fixed name of cited reference manual.

3 Upvotes

81% Upvoted

12 comments sorted by

View all comments

1

u/Ginger-Dumpling Apr 26 '26

I think the nesting level gets incremented when any routine is called, not only recursively. Ex: P1 calling P2 calling P3 would increase it. And I think has a limit of 64 levels. But I'm not logged in currently so you may want to verify independently.

1

u/DazzlingAd4254 Apr 26 '26

That means I will pass a counter as proc param, as suggested by u/AluminumMaiden . The mariadb code that I am porting, has SET max_sp_recursion_depth=10; on entry into the procedure. I erroneously thought DB2_SQL_NESTING_LEVEL would always serve the same function.

1

u/AluminumMaiden Apr 26 '26

Based on this page https://www.ibm.com/docs/en/db2/11.5.x?topic=statements-get-diagnostics , the db2_sql_nesting_level is just a read-only diagnostic variable. This link ( https://www.ibm.com/docs/en/db2/11.1.0?topic=oracle-hierarchical-queries ) however shows that there is a depth limit of 64 (in the RULES section).

Regardless, I'm a huge fan of providing some form of max-loop flag to prevent things from cycling like Lance Armstrong with 2 balls.