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

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


> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of Joe Pluta
> Sent: Friday, August 17, 2001 9:27 PM
> To: midrange-l@midrange.com
> Subject: RE: AS/400 STILL wearing the undeserved "closed system" moniker
> (was PC connection via twinax ?)
>
>
> > Actually
> > the new enhancements to the DB optimizer will be done to enhance the SQL
> > performance.   The Native methods READE, SETLL,  OPNQRYF  will
> not perform
> > as fast as SQL in the future.   The only way you should access it from
> > outside is via SQL.
>
> I've heard this said before, but I find it difficult to believe.  It flies
> in the face of common sense, at least to me.  Walk with me here.  In order
> to access a file in a keyed sequence, you must sort the keys.  If the
> sequence is dynamic, there is no way that a sort will take zero
> time, so the
> lag to get the first record is always going to be a positive,
> finite number.
>
> So, the answer to that is to make the list static - that is, pre-build a
> list of pointers to the file in a specific sequence, and then use that to
> position yourself within the file.  However, if you have
> selection criteria,
> you will have to compare each record to make sure it fits in the criteria.
> The other option is to include in the list of pointers only records that
> match the criteria.
>
>
> So, the best possible performance is to have a list of pointers in the
> correct sequence of only records matching the selection criteria.
>
> This is a logical file.
>
> So am I missing something, or has someone figured out how to make
> a compare
> actually take negative time?  Because sorts and selection require
> compares -
> compares that are not required for a hardwired access path.  Unless IBM
> develops an entirely new and faster indexing algorithm and then DOES NOT
> apply that algorithm to logical views, then there is no way - barring the
> negative cycle compare instruction - to go faster than a logical view.
>
> If, on the other hand, a new indexing algorithm is designed and it isn't
> used for logical files, I would definitely start screaming bloody murder
> because there's no good reason to have two access methods ("slow"
> and "make
> SQL look good").  IBM has been known to make those knid of
> blunders: witness
> the WebSphere fiasco.
>
> But until someone explains to me in detail how an SQL access can
> possibly be
> faster than a hardwired indexed access (and remember, kids,
> caching doesn't
> count because the exact same caching should be available to both sides), I
> don't but this particular line of speculation.
>
> Joe
>
>