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

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

----- Original Message -----
From: "afvaiv" <afvaiv@wanadoo.es>
To: <midrange-l@midrange.com>
Sent: Saturday, July 06, 2002 12:53 PM
Subject: Re: SQL using access paths NOT meant for his use ...


> 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?"
>

If you have lots of logical files, one way to have the query possibly avoid
the use of certain access paths, is to delete all the logicals and recreate
them starting with the ones you don't want used. The search order on access
paths is from most recently created to oldest.
Of course, the query could still find the logical if it searches all the
paths available. You would have to put it all in debug to find out.
If that still does not work, then create the index it's looking for, without
the select omit or even just go into SQL and create an index.
If the path can be shared on the 400, then it will create using sharing and
now your query will find (again) the most recently created path first.

> 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 ???

I am guessing here, but no, I don't think that packages work the same way on
the 400 as other platforms. The statistics that the 400 uses are not static,
DB2 and other platforms, in general are static and regenerated from time to
time like in DB2 with RUNSTATS. But the 400 looks at what's available right
now, live and on the fly.


===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"There is a crack in everything,
  that's how the light gets in.
    - Leonard Cohen