|
Thanks Vern
Gang,
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 queries.
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.
FWIW,
Dave Odom Arizona
date: Mon, 29 Mar 2004 15:25:17 -0500 from: rick.baird@xxxxxxxxxxxxxxx subject: SQL database makovers (was: Query optimizer tells me to build an....)
This talk of the difference between DDS and SQL created access paths has caused me to question long and deeply held beliefs when it came to database 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 that 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, or 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 bunch of single purpose logicals. 4. if performance problems are a concern or are encountered, logicals are 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 physical 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 (RECOVER keyword)? If so, how?
Thanks,
Rick
--------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 and tables/views/indexes. I've seen that if an LF is created over a PF and both share the same key, then the LF file description shows the PF as owning the access path. But a SQL CREATE INDEX using the same field doesn't appear to 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 coincide better, but I haven't learned them.)
Two obvious considerations come to mind -- double access-path maintenance and double space for storing the access paths.
Tom Liotta
_______________________________________________ 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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com 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.