Re: SQL Existence Check

--
[ Picked text/plain from multipart/alternative ]
Frank and Joe,

You guys, in arguing system level "nit picks" (that, frankly, nobody else
cares about), have lost sight of what the original question was.  Why use SQL
instead of CHAIN or SETLL?  The answer to this question is "THERE IS
ABSOLUTELY NO REASON".  If I must remind some of you, one of the primary
reasons that SSA filed for bankruptcy is that one or more persons convinced
SSA management that a single record SQL fetch was every bit as efficient as
was a CHAIN.  The latter was not true at the time, and is not true today.
Business application programmers choose the iSeries/400 over "sexier"
machines because they don't _HAVE_ to know whether or not ISAM, VSAM, TSAM,
or DSAM is the underlying architecture -- just what works best. Despite IBM's
_HUGE_ advances in this area, SQL still ain't it for record verification.

SQL "bigots" tend to come from Windoze shops.  Joe's example of a BOM was
correct, but for the wrong reason.  Put ten million records in that BOM file,
and see how well your SQL works for verification purposes.  The latter is
what us in the BPCS world put up with on a daily basis.  Unless you count
inquiries and reports as the entirety of "business applications", SQL is
defininitely _NOT_ an industry standard for business application programming
on _ANY_ platform...

JMHO,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-mail:  DAsmussen@aol.com

"If evolution works, why do mothers still have two arms?" -- Milton Berle

In a message dated 3/12/02 9:20:31 PM US Eastern Standard Time,
Frank.Kolmann@revlon.com writes:


> see comments inline.
>
> From: "Joe Pluta" <joepluta@PlutaBrothers.com>
> >> The industry standard is becoming SQL, (obsolescence looms)
> >> but I will try to give a reasoned response.
>
> >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.
> I mean relational or relational like databases that can
> be accessed by using 'SQL like' systax.
>
> >It's a standard for inquiries.  Inquiries are only a small,
> >rather trivial subset of business applications.
> >Try to code a price lookup in SQL, and you'll be
> >using cursors and fetch loops, which are basically just
> >poor SQL equivalents of SETLL/READE loops.
>
> 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.
>
> >>The concept of a scrollable cursor is basically just a mimic of an ISAM
> index,
> >>with poorer performance.  If SQL were meant for transaction processing,
> >>it would be called STPL, not SQL.  Try to process
> >>heterogenous sets of hierarchical data in SQL - you either need multiple
> >>cursors and FETCH/READE loops or massive JOINs that return duplicate data
> in
> >>all but the lowest order columns.
>
> '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.
> I still come accross the stuff with EDI but I avoid it using
> EDI packaged software,.... and thats exactly what I set up for
> EDI output ie 'duplicate data in all but the lowest order columns'
>
> >> As I used SQL more I found I could write programs that
> >> with the same code handled different sort sequences,
> >> multiple selection criteria EASILY.
>
> >Again, this is the class of programs known as "query", hence the name
> >"structured query language".  Try to write an MRP generation or a BOM
> >explosion in SQL, and get back to me.
>
> 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.
>
> 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.
> 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.
>
> >> Sure I can whack in an F spec for a simple
> >> CHAIN or SETLL but why should I as I have not other F SPECS
> >> and it makes the code look silly.
> >
> >What's silly is five or ten lines of SQL where a single CHAIN would do.
> >THAT'S silly.
>
> 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
>
>
> >> I am not writing code for cross platform applications
> >> but I sure know how to , do you.
>
> ><laughing>  Yes, Frank, I believe I have a bit of knowledge in
> >cross-platform development.
>
> <kow towing>
> I seen some of your stuff Joe, I am impressed and you know more
> than I do, I was trying to be general in my reply. Your weight
> can put some people off from even trying SQL by a simple
> comment one way or the other.
>