Comments (7)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 YONGLEIG commented Permalink


2 lelle12 commented Permalink

Nice article (as usual). Perhaps it is worth mentioning that the recursion described in this article adds more expressive power than what is possible using CTE's (and I assume connect by as well), nested recursive calls comes to mind.

Is there a deliberate reason to limit the types of recursion that can be expressed within a CTE? I'm thinking like almost anyone have the ability to ask a recursive query, but fewer people are allowed to create a recursive function, or is it just a coincidence due to the fact that no one really needs more power than what is possible in a CTE? I can honestly say that I newer felt a real need (other than for fun that is :-) to express something more complicated than what's possible within a CTE, so the question is just out of curiosity.

3 SergeRielau commented Permalink


Good point on the expressiveness.
CTE recursion was added before my time and has never been re-opened with the exception of CONNECT BY.
The SQL Standard went beyond IBM's initial support to also allow for DEPTH FIRST recursion.
Also I think some of the limitations we currently have are unnecessary.
For example it would be fine to allow OLAP, ORDER BY and aggregates.
Allowing more fancy FROM clauses (perhaps multiple references to the recursive view) would semantically trickier, I think.
As you note there seems to be little pressure to enhance.
If I were to re-open CTE I would support them in general FROM clauses.

4 BigBrett58 commented Permalink

I think this is very interesting and believe I understand how this works. I've tried to compile a couple of your examples without success. Specifically, I tried with the module with function and the function with the dynamic cursor. Both attempts ended with basically the same error. The dynamic cursor function would compile, but I received the error Lookup Error - DB2 Database Error: ERROR [42887] [IBM][DB2/LINUXX8664] SQL0390N The function "schema.FIB" resolved to specific function "xxxx" that is not valid in the context where it is used. The function within the module had the same error when trying to add the function body. I'm working with DB2 LUW 9.7 fix pack 5. It is a partitioned database. I've messed around quite awhile trying to figure out some way to get it to work. I can get it to compile within the module if I change to "begin atomic" and change CASE WHENs to IFs, but the result is the same when I try values(m.Fib(10); So, best guesses in descending order are that this is supposed to be for single partition database, for DB2 LUW 10.1, or DB2 Z/OS.

I might not be able to use this SQL, but I think it looks fun to play with.

5 SergeRielau commented Permalink


Compiled SQL Functions are not supported in DPF.
It's a rather fundamental restriction independent of recursion.
When you go to BEGIN ATOMIC you are using inline SQL PL which is supported.
But inline SQL PL has many functional limitations such as no dynamic SQL.

6 BigBrett58 commented Permalink

Thanks for the reply. I'm actually happy to know that it wasn't possible, because I thought I tried everything I could think of. This academic exercise lead me to start taking a look at modules. I started to rewrite a pretty complex process I made 18 months ago. I really enjoy blog entries like this one. It gets my creative juices flowing.

7 pbarbas commented Permalink

Very nice article. I was trying to use this implementation but with a small diference:

I was changing signature from: CREATE OR REPLACE PROCEDURE Fib(IN n INTEGER, OUT res DECIMAL(31, 0))
And it does not work.... any inputs on this?