Re: Efficient Code

["Buck" <buck.calabro@xxxxxxxxxxxx>]

>> "would be nice to have" report needing to show the files
>> that could share the same access path, the ANZDBF*
>> commands aren't much better than running the DSPFD
>> commands.  There'd still be (a lot of?) programming
>> to do to put it together

> I'm unclear as to why a simple query wouldn't do the job??  Is the
> information lacking in DSPFD *ACCPTH, to find (not only *LF relationships
> but) number and names of the actual indexes being created by the system??

TYPE(*ACCPTH) describes the key list, but not whether the file is actually
sharing an access path.  TYPE(*MBR) will tell you what file owns this access
path.  The programming bits (devil in the details) come in when you want to
identify the candidates for access path sharing, taking into account that
you can't share a join file's access path, that physicals may have an access
path, that there may well be intentional cross-library dependencies and that
in order to share an access path you need to build the most detailed access
path first as well as knowing that you don't need to declare one of these a
'candidate' if it is already sharing the 'right' access path.  I was unable
to make a simple query to do that.

Walden posted

>I missed that 51-logical's part, ouch. Then again, you could have 51
>logicals and one index, not that bad.
>
>I've seen cases where index sharing was _possible_, but no
>one had done what was needed to enable it. If you have 4
>logicals, keyed:
>
>A
>A, B
>A, B, C
>A, B, C, D
>
>And you created them in that order you'll have 4 indexes,
>but if you created them in the reverse order you'll have one.
>_BIG_ performance difference. It can be worth it to examine
>the logicals for index sharing possibilities too

And it seemed reasonable to guess that if there was a commercial product
available which did this very thing (not only identify the candidates,)
someone on the list would be interested.
  --buck