RE: AS/400 STILL wearing the undeserved "closed system" moniker (was PC connection via twinax ?)

Which further begs the question: what exactly is "set based processing"?
When do you do set based processing?  For queries?  For update?  Do you use
set-based processing when you're allocating inventory?  Or calculating
prices?  Or doing MRP generations?

I've always said that for queries, SQL can definitely perform quite well.
It's especially good at ad hoc queries, where there is dynamic access to
data.  Even in the paper you mention, they talk about query optimization in
terms of eliminating rows:

"The primary goal of the optimizer is to choose an implementation that
quickly and efficiently eliminates the rows that are not interesting or
required to satisfy the request. Normally, the query optimization is thought
of as trying to find the rows of interest. A proper indexing strategy will
assist the optimizer and database engine with this task."

Notice the goal of eliminating rows.  With a proper logical view, there are
no rows to eliminate.  Now, here's the example they point out:

SELECT CUSTOMER_NAME, ORDERNUM, ORDERDATE, SHIPDATE, AMOUNT
FROM ORDER_TABLE
WHERE SHIPDATE IN ('2000-06-01', '2000-07-01', '2000-08-01')
AND AMOUNT > 1000

Um, okay.  That's an important use of SQL, but it's not one I use in
transaction processing.  In most cases, transaction processing involves
processing all the records in a given keyed subset, and doesn't involve
field-level comparisons to eliminate rows (at least not if I know how to
design my database and write my code).

So, to my mind, proper database optimization can indeed speed access for
things like selecting, joining, grouping and ordering.  But I submit that
none of these are typical actions in a transaction processing environment.
Yes indeed, for queries, customer support, executive information,
statistical analysis, and all things of that data mining ilk, query
optimization is a plus.  But how SQL can outperform a logical view for
non-constrained access is still a little beyond me.

I guess because I don't believe in magic, I'd like to know what the basic
concept is that allows SQL to outperform an indexed read for pure
transaction processing tasks.

Joe



> -----Original Message-----
> From: John Taylor
>
> Joe,
>
> I agree with you if you're talking about single record key
> positioning, but
> READE/SETLL/OPNQRYF imply (to me) set based processing. SQL can often
> outperform native access for sets. Of course, OPNQRYF itself uses the same
> underlying SQL support, so I wouldn't have included it in the
> same family as
> the native opcodes.
>
> Here is a link to an interesting read about indexing in DB2/400, if you're
> interested in nitty-gritty details:
>
> http://www.iseries.ibm.com/developer/bi/documents/strategy/strategy.pdf
>
>
> John Taylor