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

There is also a stored procedure that will clear the SQE Plan Cache.
If you want to risk, that all plans must be built by scratch.

IMHO you are on the wrong boat with suppressing system activities.
I'd first analyse what causes those thousands of advices and whether are
they really necessary, i.e. do you really need to perform all those access
to the meta data, are this dynamic SQLs would extended dynamic SQL help etc.
Once a MTI is generated it can be used by any SQL statement and an MTI is
only generated if there is no other way to access the data.
And if an MTI is generated on an empty table (it was already discussed in an
other forum!) it is because in future this table may contain data.
Why at all do you have empty tables? That does not make sense either.

Instead of discussing your "problems" in any forum, I'd ask for support at
IBM.
Let them analyze your system and SQL and explain what and why something
happens.
I assume YOUR problems with SQL are somewhere else.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
CRPence
Gesendet: Wednesday, 02.9 2015 21:35
An: midrange-l@xxxxxxxxxxxx
Betreff: 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].

--
Regards, Chuck

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.