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