RE: SQL Existence Check

** I have added my comments using **
** Walden thanks for your reply, it was the only one
** that was relevant to my question,
** is FETCH 1 ROW implemented on V5R1
** I did not intend a SQL vs ISAM discussion, this is my final
** reply, Joe its been fun.

>
> >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...


** agreed

> 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.
** I have rewritten PRMS pricing, not with SQL but I could do that to.
You've never written a bill of materials explosion.
** Yes I have, both single level and indented, and could recode using SQL.
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.

** agreed, but it is only a matter of time before the SQL developers
** get it right



> '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.

* its just a matter of definition to me 'heterogenous sets of hierarchical
data'
* means Header/Detail/Comments etc all in the same file.
* It can be processed using seq read on Input file and write to a new
Output file.
* matched against incoming heterogenous transactions.
* Its a very archaic method.
* Variable record layout files are still used for EDI data.

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.

* I agree file normalisation is the proper method to use.


> 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.

*
*  Since when have Querys not been a major part of any business app.


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.

*** I have not used Java yet, otherwise agreed
*** Tell me something. I heard JAVA uses UCS-2 and doubles disk req.

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.

*Joe I know what you mean, I too have worked on IPICS MAPICS PRMS and BPCS.
*Yes I know what all those terms mean, but what does this have to do
*with my original simple question, and yes I could use SQL to implement a
MRP.


> 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.


*Code legibility is very much related to programmer competance as is
*performance,(both the programmers and the software)
*but it has nothing to do with the language used.

> 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.

Checked what, I assume you mean check the result of the select.
or add a D spec for WCFCUS.
Is not indicator 91 automatically set that you use later, well how
different
is that to SQLCOD and SQLSTATE being automatically set, that you use later.



> 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!

**  BPCS bit me too, IBM solution is get a bigger AS400.
**  We got a bigger AS400 and changed some SQLs back to CHAIN etc.

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.)


** largely I agree, I suppose IBM is reducing costs by trying to
** adopt one method of database access, and SQL is it.

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