RE: SQL recursion -- MIDRANGE-L

One text that I have found to have a clear explanation on the recursive
query in particular is "Using the New DB2" by Don Chamberlin. It may be
dated, but recursion was in the LUW stuff as far back as 1996 or 95. He
is one of the creators of the SQL language and was still working with
DB2 UDB for IBM as late as 1996.

On Thu, 2006-09-28 at 11:13 -0400, Chris Payne wrote:

Is there a good place to read about recursive SQL?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Thursday, September 28, 2006 10:50 AM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL recursion

Yeah, this is a pretty snazzy bit of sleight of hand.  The SQL committee
added CTEs to the language specifically to handle recursion.  And it
works
very well for a simple BOM.  Of course, it gets a little hairy when you
have
things like batch quantities and co- and by-products, so it's not for
everyone.  But as long as your rules are simple enough that you don't
get
lost in a maze of CASE statements, a good SQL recursion is probably
going to
greatly outperform native I/O, especially in a batch processing
environment.

I'd be interested to know how well that same statement performs against
native I/O for exploding a single BOM.  I bet there's still a point
where
native outperforms SQL, probably when you're dealing with less than 100
records.  But I could be wrong, especially if i5/OS manages to cache
some of
its index information.

Joe

From: rob@xxxxxxxxx

Yeah, I remember that "back in the day" stuff.  The problem is that

many

people, once told something, never change.  There are still people

that

believe that.  There are still people leery of each LF they add

because of

performance considerations  (granted I'm not advocating 100LF's on 1

PF

but let's not swing to the other extreme).  And a lot of other items

that

were true "back in the day".

And a coworker here just wrote a V5R4 recursive SQL statement to

explode

MBM that blew the socks off of traditional RPG logic.

with Exploded(Parent, Child, Qty, LVL, TopParent) as
  (select BPROD, BCHLD, BQREQ, 1, BPROD
    from MBM
  union all
  select BPROD, BCHLD, BQREQ*Exploded.Qty, LVL+1, TopParent
    from MBM, Exploded
      where BPROD=Child)
  select * from Exploded
 order by TopParent

For this to work in our database, the QAQQINI file must have setting
IGNORE_DERIVED_INDEX='*YES'



-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.