Re: AW: Index advisor and system cross-reference file

On 02-Sep-2015 13:05 -0600, satya tiruveedhi wrote:

On 02-Sep-2015 08:06 -0600, CRPence wrote:

On 02-Sep-2015 04:41 -0600, satya tiruveedhi wrote:

<<SNIP>> IBM has a PTF to disable journaling on SYSIXADV. If
journaling can be disabled because it is unnecessary, why not
provide option to disable the update itself when it is
unnecessary.

If the desire is so great to prevent the updates <<SNIP>>

Otherwise, apparently [as can be *inferred*] per the following
TechNote document Reference# N1011448 [Historical Number
605100633], there is another option [¿that requires an IPL?] to
disable the feature quite generally:

[http://www.ibm.com/support/docview.wss?uid=nas8N1011448]
"...
1. ADDENVVAR ENVVAR(QIBM_NO_INDEX_ADVICE) VALUE('') LEVEL(*SYS)
2. IPL
..."

To re-enable the feature for a future IPL:
1. RMVENVVAR ENVVAR(QIBM_NO_INDEX_ADVICE) LEVEL(*SYS)
2. IPL

Thanks for the information. I will try environment variable in test
LPAR. It will definitely help as index as advised zillion times in
one month. kidding :) The number is just over one billion. Again,
thanks for the information. Hopefully, this will not break something
else.

Will this <ed: activation of the system-level Environment Variable
(ENVVAR) named QIBM_NO_INDEX_ADVICE] stop creation of MTI also? If it
does stop creation of MTI, it will be very useful. Finally we can get
rid of all those MTIs on tables with zero rows and give DBA team a
chance to analyze the impact of full table scans on tables with zero
rows :)

Again, I infer, as I have no knowledge of the support; I merely located the TechNote document N1011448 having performed a web search on the file name of SYSIXADV. Almost surely the effect will _only_ stop logging Advice. The Maintained Temporary Indexes (MTI) almost surely will still be created when deemed relevant by the SQL Query Engine (SQE). Note that all MTIs will disappear for the next IPL [following a power-down or a crash].