RE: SQL Existence Check

> From: Frank.Kolmann@revlon.com
>
> >SQL is not an industry standard for business applications.
>
> Pray tell what is the standard for business apps , I mean
> not simply for AS400s, but all systems.

ISAM, at least until IBM kills it.  The AS/400 is the standard for midrange
business applications and in mainframes, the standard is COBOL, last I
heard.  Both use ISAM.  Until recently, SQL-based systems were hardly the
norm, and there still aren't that many pure SQL business applications (by
application, I mean an entire suite, like an ERP package).  SQL is slowly
catching up with basic ISAM, with things like scrollable cursors, but it's
still not the standard, not yet.  This of course is a highly subjective
opinion, and mine is heavily colored by my decades of experience on the IBM
midrange platform.  Perhaps there are dozens of fully functional ERP
packages running on SQL that I don't know about.  I hope I never have to
maintain one...


> I have written complete business apps with just using SQL.
> Whats wrong with cursors and fetch loops, why poor.
> They work and I have not measured performance but with
> good indexes performance is satisfactory.

You've never written an MRP generation using SQL.  I doubt you've written an
order entry application with any sort of real pricing, such as promotions
and deals.  You've never written a bill of materials explosion.  These are
real business applications, and all require abilities outside the capability
of a SELECT statement.  And while declaring and opening a cursor and
fetching records might be getting better, they still don't match the
performance of a CHAIN.


> 'heterogenous sets of hierarchical data' I stopped setting up such
> databases when the S3/15D died. Not being personal but who in their
> right mind would set up an AS400 application in this manner.

Well, for one, anybody who write an order entry system.  They call this type
of data "order header" and "order detail".  Not sure how you store it, but
those are distinct entities.  In an industrial strength system, throw in
partial lines for shipments and backorders, and you now have three levels in
the hierarchy.  Since you're in your right mind, please explain to me how to
store this information in anything OTHER than three different tables.

If, however, you're insane like me and you do set up three different files,
then getting all the information for one order using SQL requires either
three cursors or a massive, data duplicating JOIN.  Please tell me another
way to do it, and I'll be thrilled.


> You know as well as I do that SQL is more than 'query'. To get
> stuck on semantics is not what I want to discuss. Anyhow whats
> BOM got to do with coding powerful query applications.

There are "powerful query applications" and then there are business
applications, such as BOM, MRP, and order entry.  These are two distinct
subsets of the class of software problems.  Other subsets include user
interfaces, device drivers and optimizing compilers.  Each has its own
distinct needs and requirements, and each has tools that are best suited for
the problem.  Query applications are very well suited for SQL.  Business
applications are not.

The AS/400 was wonderful in that many of the things we needed were done for
us.  For instance, unlike most platforms, programmers had to do little or no
UI programming, this was all taken care of in the the DDS and in the
workstation middleware, which in turn was several pieces, including the
workstation gateway software and the actual firmware on the 5250 device.
Now, with the advent of the browser, we once again have to do some UI
programming.  Personally, I find Java to be the best language for that
particular piece.  So I'm not averse to learning a new language to fit a new
requirement.

Or even to use a new tool that's better suited for an old requirement.  SQL
is great for flexible queries.  In many cases, it probably is more powerful
and certainly is more flexible and easier to use than the old technique of
OPNQRYF.  However, for the core of any business application, the coding of
business rules specific to a company's operations, the rules that run BOM
and MRP and allocations and pricing, RPG using native I/O is still the best
language.


> Dont have time to write an MRP.
> I have not used SQL for BOM explosions but I could easily
> replace the CHAIN operation with an SQL SELECT. Do you mean
> that performance is not so hot, perhaps so, but setting up
> appropriate indexes helps. Ditto MRP gen which is basically
> multilevel BOM explosion then summarised and a bit of date calcs.

Uh.  Yeah.  And a compiler is basically just a parser with some symbol
generation.  I suggest you try actually writing an MRP gen someday, just for
fun.  Remember a few of the following terms: order policy, coproducts and
byproducts, batch balancing and demand codes.  Implement those using SQL,
and I'll be truly impressed.


> There is more than one way to use SQL to achieve this, but I
> would probably use intermediate files and multiple passes rather than
> set up a complex SQL to do this in one pass.

By the time you managed any one of these in SQL, a competent RPG programmer
would have written a program that was easier to modify for changing business
conditions, outperformed whatever you wrote, perhaps by an order of
magnitude, and been working on another application.


> FRCFRCM02  IF   E           K DISK
> C     RCFKEY        PLIST
> C                   PARM                    KCFCUS
> C                   PARM                    KCFRUM
> C                   Eval      KCFCUS  = RCFCUS
> C                   Eval      KCFRUM  = RCFRUMA
> C     RCFKEY        CHAIN     RCFRCM02                           91
>
> C
> C/Exec SQL  SELECT  DISTINCT RCFCUS  INTO :WCFCUS  from RCFRCM02
> C+ WHERE RCFCUS =  :RCFCUS   and RCFRUM = :RCFRUMA
> C/End-Exec
>
> 7 vs 3

Different example.  We were talking about doing an existence check.  And you
still haven't checked your SQL code.


> Your weight
> can put some people off from even trying SQL by a simple
> comment one way or the other.

Just because I say SQL isn't perfect for everything, people who have fallen
in love with SQL start thinking I hate the language.  Nothing could be
further from the truth!  I LOVE SQL!  I use it EVERY DAY!  I am CONSTANTLY
using SQL for ad hoc queries!  One of my programs uses SQL against an IBM
system file, just in case they change the layout.  There are some wonderful
reasons and places to use SQL - what I argue against is the INAPPROPRIATE
use of SQL.  There is no good reason to start rewriting powerful, proven
ISAM logic into SQL, just because someone says it's a standard.  IT'S NOT!
It is my firm belief, and I have yet to find someone able to prove me wrong,
that an average RPG programmer can always write complex business logic
faster using native I/O, and get better performance doing it, than an
average SQL programmer.  To be fair, an SQL expert can probably tweak it in
certain circumstances and perhaps even get it to outperform native I/O, but
not without analyzing index usage and JOIN orders and selection criteria and
writing SQL code that would very easily be broken by the next person to
touch it.  On the other hand, with native I/O you're right next to the
database doing what you need to do to access and update the data, and it's
harder to break it and certainly easier to fix.  (And while I admit the
easier to break/fix argument is somewhat subjective, some of the more
sophisticated SQL statements I have seen written in order to get top
performance out of the database certainly seem to THIS poor old dinosaur to
be a lot more complex than the same ISAM code.)

The extra layer of indirection that SQL puts on this stuff is fine for
applications that aren't mission critical, such as ad hoc queries, but
adding 50% overhead to a monthly batch run that already takes 15 hours is
absolutely unacceptable, especially if the only reason to do it is so that
it's "portable".

Let me be absolutely clear about this: I think SQL is great.  But I treat it
with a bit of caution.  In many ways, it's something like a vaccine.  It has
a lot of potential for a lot of good.  But used incorrectly, it can kill.

My thoughts, that's all, and of course your mileage may vary.

Joe