Re: SQL using access paths NOT meant for his use ...

Great question. I had not thought of this.

I think you are right about the optimizer. It will look for the index that
best supports record selection and ordering. The fact that your logical
files are in a different library does not matter. These relationships are
kept for each file - you can use DSPDBR to see the relatonships.

As far as fields are concerned, I don't think you lose anything. I'm not
sure of this - others will know better. I believe that the index is not
used for retrieving data (unless all the fields you want are in the key),
only for record selection and ordering. It's not quite the same as reading
a logical in RPG.

If possible, you could STRSRVJOB on one of these jobs (assuming it is not
your own interactive job), then STRDBG. This will put SQL optimization
messages into the job log, where you can see what it did to choose what it
chose.

The fact that you have not seen any problem yet, suggests that you will not
see one. The only concern I might have is if you have select/omit in your
logical. But this is also not a problem, because it will not be used if the
select/omit criteria do not match the WHERE clause of the SELECT.

At 01:11 AM 7/5/02 +0200, you wrote:
>Today we discovered something that strikes me:
>
>We are using BPCS, but I think this has nothing to do with it.
>Anyway, many BPCS programs use embebed SQL ... Not good performance at
>times, but it works.
>
>For some different reasons, I have created some logical files over BPCS
>physical files, these logicals just intended for some other applications
>that will use part of the BPCS data.
>
>No problem with that. But, occasionally, we are having some "strange"
>problems with some ot the BPCS data , programs, ...
>Today we found out that doing a DSPJOB of a certain BPCS job, looking
>into "Open files"... we were surprised to see some of our "logicals" as
>being used by the BPCS job !!!
>
>These "logicals" are on a separate library, NOT included, by no means,
>in the jobs library list !
>How come is BPCS using them ?
>These logicals are NOT secured (not protected as public use of
>*EXCLUDED) for different reasons... So, in theory, anyone could use
>them, though normal users will never have access to them. Still...
>
>I guess, when using SQL from a BPCS program, if the Optimizer finds
>there is "somewhere" (???) (my library ???) a logical file that has an
>access path that would be very convenient for the work it has been asked
>to perform, maybe it is using that logical access path even though it is
>not in the Library list ???
>
>IF this is the case (I'm just guessing, since can't find a better reason
>for what I've mentioned we saw when doing the DSPJOB / Open Files ...),
>the problem comes from the fact many of these "private logicals"  do NOT
>include ALL the fields from the physical file, but just those fields we
>felt were convenient for our needs.
>So, if SQL is using this access path for "its own convenience...",
>some/many of the fields it expects to read will be read as
>blanks,zeroes, nulls, or whatever, and the the BPCS program will
>fail/produce erroneous results...
>
>Am I correct interpreting SQL might be using our logicals "as convenient
>paths" even though they are not in the BPCS job's library list ?
>If the answer is YES, how can we prevent it without recompiling programs
>(BPCS source is, of course NOT available) nor going into securing these
>logicals are restricted access thru special authoritites, groups, etc?
>
>Any suggestions will be welcome! Regards,
>-------------------------
>Antonio Fernandez-Vicenti
>afvaiv@wanadoo.es