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'