Re: Performance question and SQL SERVER - RESOLVED

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.