Re: Performance question and SQL SERVER - RESOLVED

[CRPence <CRPbottle@xxxxxxxxx>]

On 06 Jun 2013 10:03, rob@xxxxxxxxx wrote:
> <<SNIP>> apparently one <ed: a QAQQINI database *FILE> in QUSRSYS
> is not a default. At least not currently.

  Correct.  IBM does not ship a copy of the file in QUSRSYS; and none 
should exist, except when explicitly created, according to the specific 
requirements of each individual system.  A QAQQINI duplicated into 
QUSRSYS is only supposed to be done when there are specifically 
overridden values [from *DEFAULT] that are to be in effect system-wide. 
 There should be very few situations and thus very few systems, for 
which any system-wide overrides should remain in effect.  Typically the 
overrides would be applied only when\where applicable as best is 
possible *without* using the system-wide overrides; i.e. to establish a 
QRYOPTLIB specific to the applications or environments in which the 
overrides should be in effect, leaving all other activity unaffected by 
possibly undesirable overrides.

  And that is the crux of the matter... that a system-wide override can 
impact [existing or future yet to be coded] activity that might best not 
get overridden, and to deactivate the possibly undesirable overrides 
requires establishing a QRYOPTLIB with [more of] the defaults 
established.  As I had alluded, *ideally* the file that effects 
system-wide overrides could and would be deleted, *but* having failed to 
include additional commentary to clarify... such that overrides would be 
more directly, selectively applied [e.g. from a particular QRYOPTLIB 
when requested], rather than generally applied [i.e. system-wide via 
QUSRSYS]... thus avoiding having to selectively deactivate any 
system-wide overrides that are or possibly not yet known to be either 
problematic or undesirable to other activity on the system.

> Whether you need/want one there is another matter.

  And the "could" in my comment is very relevant in that regard. 
Effectively, if the system-wide override is needed [or just wanted], 
then the DLTF QUSRSYS/QAQQINI seems almost unambiguously incompatible 
with "could delete the file".  However if there are no rows that are 
other than *DEFAULT, or when the only non-*DEFAULT settings have the 
current setting the same as the system *DEFAULT, then the file can and 
should be deleted because its existence is both overhead and confusing 
for its existence [while effecting nothing, so purposeless].

  Often the file in QUSRSYS was originally created only because someone 
running debug did not like the diagnostic message getting logged, 
suggesting that the file QAQQINI in QUSRSYS was not accessible... and 
creating that file is just one possible recovery.  Ugh! :-(

  Another typical reason for the file created in QUSRSYS was to effect 
the one override to IGNORE_DERIVED_INDEX = *YES but over a release 
transition [to IBM i 6.1] the new default becomes *YES, thus the purpose 
of that file with just that one setting is since defunct.  In the IBM i 
7.1 docs, that parameter seems no longer to be documented, so apparently 
the row is since ignored by the system; and possibly, but unlikely, the 
insert trigger no longer even allows that parameter to be inserted.

> Question everything. Don't be like my sister who used to forward on
> emails: "delete command.com from your PC if you have it because it
> is a virus" or some such thing.

  Like unquestioningly performing RCLSTG even though there is no known 
requirement to do so ;-)

Regards, Chuck

> Mike Cunningham on 06/06/2013 12:51 PM wrote:
> > <<SNIP>> <ed: The QAQQINI *FILE> in QSYS is dated 8/10/09 V7R1
> > and QUSRSYS 22/21/06 V5R3. So I should probably delete the one
> > in QUSRSYS?
> >
> > CRPence on Thursday, June 06, 2013 11:45 AM
> > > In general, a QAQQINI should *not* even exist in QUSRSYS; i.e.
> > > none should exist to be deleted. Ideally any copy of QAQQINI in
> > > QUSRSYS could and would be deleted on most every system. The one
> > > to *not* delete is the file in QSYS.