RE: Single record access really required (was RE: Views and -- MIDRANGE-L

Dave, let me expand on the security aspect. On the iSereis, security is a part of every object, and everything is some type of object. It is not a special security application or component of another applications, as it seems to be in VM and MVS.

About native vs. SQL, let me make a statement that I've not thoroughly tested empirically but is based on my experience and understanding of the iSeries, viz., I expect repeated fetches by key (one at a time) to be slower with SQL than with native IO over a keyed logical file (index). This is because the B-tree structure of logical files is VERY efficient for this task, where SQL has a longer code path before it gets to actually retrieving the record. You DO say that SQL is not designed for this kind of thing, I agree. It'd be interesting to see a FETCH KEY over a scroll cursor, but that is not there now - indicative of the suggestion not to use SQL for random keyed access.

Now using SQL to fill a subfile could be a different animal - repeated FETCHes from a SELECT with a greater-than predicate (sequential by key) is like a SETLL followed by multiple READs. In this case, the cursor can be opened once and all the needed records read. And there are ways to do blocked FETCHes that can help even more. Using FOR n ROWS on a fetch in conjunction with an MODS is just neat for subfile page-at-a-time processing.

About the optimizer - one thing that could run faster than native keyed IO would be a table scan when the physical is in the same order as the index would provide. If all the records were to be read, it might be faster to create a temporary copy in the order of the ORDER BY. And if all the SELECT columns are in the key, an index-only access could be quite a bit faster, depending on hte length of the key compared to the length of the physical files record.

But for less than all of a large number of records, native keyed access would usually win for speed, I believe. We will see, as we compare and follow Joe's activity.

Regards
Vern

At 01:53 PM 7/27/2004, you wrote:

A couple of questions on this argument:

What does IAAI stand for and how are they involved with performance
issues?

Is DB2/400 like VM & MVS DB2, such that when you create tables inside
of DB2 you can't read the data except with SQL and the security inside
of theDB2 engine?  If that is true, then I prefer SQL for programming
applications, at least from the security aspect.   And, if the DB2/400
optimizer is anything like the VM/MVS DB2 optimizer, I'd say that you'll
get better performance with DB2 over the keyed-sequence record-at-a-time
access of the native 400 I/O but usually only IF you have large numbers
of records.  The reason for this is because the DB2 Optimizer makes
decisions on how to best get your data, and those decisions may change
day-to-day, whereas the keyed sequence approach goes through keys and
follows much the same path regardless of how circumstances have changed
and may not be the best performer.   It is important to make
apples-to-apples comparisons as far as the data and the application
architecture.   I hope, if the IAAI is into performance comparisons the
comparison test are set up in a fair manner using the coding access
strategy best for each access method and not forcing a key-sequence
approach using SQL or vice versa.

FWIW,

Dave Odom

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.