Tom and everyone, I changed the subject line, because I'd like to start a new thread to discuss the pros and cons of when and where to build access paths (either LF or view/indexes), and how they relate to our environment today. I've been an RPG hack since the early eighties. While I've never been on the bleeding edge, I tend to adopt newer technologies faster than most. Most of my time has been spent in consulting for numerous clients or working for a software house, so my opportunities to implement new stuff has been dictated by who I was working for at the time. I've done my share of application database design (though not as much lately), but the conversation of the previous thread has made me question all my past assumptions. In my experience, for the most part, i've designed with these few simple rules in mind. 1. Nearly all permanent physical files had keys, unique keys if appropriate (whether enforced via UNIQUE, or programmatically) 2. Because of the overhead involved in maintaining logical file access paths... a. If SQL wasn't available, logical files were built mostly for inquiries or programs that run daily or more. b. If SQL was available, some of the above could/would be replaced by embedded SQL. 3. Rarely have I used record selection in a logical- for this basically creates single purpose access paths, and in my experience if the only program that uses it goes into disuse, the logical rarely goes with it. 4. Opnqryf was used for most batch record selection. 5. Expected performance issues can override all of the first 4 rules, based on file size, expected usage, etc. 6. Unexpected performance issues are handled on a case by case basis. all of the above has been true, and has work for me with varying degrees of success for 20 years (except for the SQL stuff, which I added to the mix a few years back). I'm not trying to write a book here, but I would like to have a discussion on some rules of thumb to follow. specifically, when designing new databases, are you exclusively using dds, sql or a combination? those of you who work exclusively with sql, when working with legacy systems, have you replaced logicals with views? added views over top of existing logicals? This is a topic of great interest to me, and any guidance would be greatly appreciated. Thanks, Rick -------original message----------- 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
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.