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

Thanks to all who responded to my original message.

All the answers I got so far (do not reproduce them to shorten this new
message) do coincide as for the fact that YES, SQL will use my logicals if
"more convenient..." than the existing BPCS logicals.
Also, all answers coincide saying, that there should be no problem, even if my
logicals do not have all the fields of the physical record, as SQL will only
use the access path, not the fields structure but the fields from the file
mentioned in the SQL statement. That's good news.

BUT..., some answers cautioned me ... "as far as your logicals do not include
Select/Omit  criteria...".
Well, YES, my logicals DO HAVE some Select/Omit conditions... So, that may be
the reason for the problems we are getting occassionnally! We HAVE HAD some
problems, we could not figure out, since we were not aware of the problem until
many days after. IF BPCS sometimes uses my logicals, which include some
Select/Omit criteria, then I guess this COULD really produce erroneous results,
and nobody would become aware of, until much later ???

So, coming back to my original question,
   "how can we prevent it without recompiling programs (BPCS source not
available) nor going into securing these logicals, restricted access thru
special authoritites, groups, etc?"

Would creating specific SQL Packages be of any good? I think this should not be
the way either, since I have no idea before hand of how many hundreds
(thousands?) of different SQL sentences BPCS may have included, even some not
fixed ones but dynamic thru "Prepare..." etc  So ???

Even if I went into securing these logicals for restricted access , THIS WOULD
NOT BE EITHER THE SOLUTION, since some people, who are authorized BPCS users,
will also use some additional programs we are providing for other applications
that will use my new logicals, so they would also need to be authorized to them
, and we would fall back to the original question, how to prevent BPCS SQL
using these logicals(since have Select/Omit NOT MEANT for normal BPCS work ...)
when they are not in the current Library list !!!

Any ideas are welcome! TIA

Am including my original note just in case some one wants to see thru the
thread
--------------------------------------------------------------------------------

> 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