Was there any more discussion on this subject, especially by someone
that truly understands the differences of how the iSeries handles flat
files built using DDS vs. how the iSeries and its DB2/400 engine handles
the tables and indexes built using DDL(SQL)?   I'd like to see a
technical document from IBM Rochester and/or IBM Santa Teresa Lab (DB2
build site ??) and/or IBM Toranto (DB2 build site) that addresses all
the ins and outs and how the iSeries handles this subject.

I suspect if the DB2/400 engine handles things anything like the
mainframe (DB2/MVS, DB2/VM and DB2/VSE), you'll find the OS/400 and
DB2/400 handle access to data more efficiently than the traditional flat
file DDS data access via the old key construct as all this relates to

However, since the OS/400 is a different breed of cat relative to the
mainframe operating systems, I don't really know.   Which is why I'd
like IBM to speak about how things really work.

I can tell you one thing, the faster iSeries folks move to a true
relational data base design vs. the traditional flat file model, the
better off they will be especially as they get pushed more toward
decision support systems for management that usually includes data
marts, data warehouses, data mining, OLAP, and more.   I would also
suspect, as the iSeries and the pSeries come closer in their use of the
same processors and hardware architecture and as something like Linux
gets more popular because of economic issues, you'll see most
application products built to a RDBMS of some sort, probably DB2 and
Oracle.  Therefore, the quicker shops move their application development
toward the true relational model, the better.   


Dave Odom

date: Mon, 29 Mar 2004 15:25:17 -0500
from: rick.baird@xxxxxxxxxxxxxxx 
subject: SQL database makovers (was: Query optimizer tells me to build

This talk of the difference between DDS and SQL created access paths
caused me to question long and deeply held beliefs when it came to
design on the iseries.

When designing databases, i'd always tried to follow these few simple
guidelines (others may disagree with them, but let's not quibble over
part - it's always worked pretty well for me).

1.  most physical files have a key, a unique one if appropriate.
2.  logical files were built for any access paths needed for inquiries,
batch pgms that would be used daily or more (generally).
3.  everything else would be handled via opnqry.
3.  opnqryf would be used for record selection rather than creating a
of single purpose logicals.
4.  if performance problems are a concern or are encountered, logicals
created as indicated to help out.

How does the apparent superiority of SQL generated access paths change
these rules?

Would you make a point of replacing existing logicals with SQL indices
where possible?

Would you build SQL indices and views next to any logicals (or
keys) that can't be directly translated to SQL?

If not (or not always), why not and/or when?

and one more question...  Can you specify on an SQL view or index the
Access path maintenance  (MAINT keyword) or Access path recovery
keyword)?  If so, how?



--------original message---------
midrange-l-request@xxxxxxxxxxxx wrote:

>   1. Re: Query optimizer tells me to build an access path that
>      already   exists (Clare Holtham)
>That's one of the indexes you ALWAYS want to build.

This subject does show a glimpse of the differences between PFs/LFs
tables/views/indexes. I've seen that if an LF is created over a PF and
share the same key, then the LF file description shows the PF as owning
access path. But a SQL CREATE INDEX using the same field doesn't appear
share the existing PF access path; it seems that a brand new one is
created. (There might be ways of getting LFs and SQL indexes to
better, but I haven't learned them.)

Two obvious considerations come to mind -- double access-path
and double space for storing the access paths.

Tom Liotta

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.