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

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

On 03-Sep-2015 19:28 -0600, Kevin Wright wrote:

<<SNIP>>
Action C (from the rest of the list) is "to get your applications
from to stop querying the system tables so much", so that the
index advisor will stop performing Symptom B, and the questions
hence are aimed at getting to that.
<<SNIP>>

* Index advices are also reported on test LPAR (7.1) which has no
business activity or development activity. So we can not blame these
advices on application. This is mentioned in my first post for this
thread.

Meaning to suggest that the same "query [that] is executed by QDIRSRV" and still being run in that [apparently LDAP] server job? Although there was allusion to /other/ queries, that one query of SYSTABLES was the only example explicitly given in the OP.

* I have given multiple reasons while asking for setting to disable
"Index Advisor". One of them is indexes being advised high number of
times. Rob pointed out that there might be problem with application.
I told him that I will investigate it. If someone gives a reasonable
explanation, I am ready to verify it and act on it.

Has the reason for the "query [that] is executed by QDIRSRV" ever been investigated? Seems an appropriate response, to investigate, given the "high number of times" and all the work that leads to that advice and all the work to actually run the query are still going to transpire, until\unless that query stops getting performed. An investigation of the purpose and validity\need of that query to run, for a legitimate purpose of effecting proper results vs the query being run unnecessarily, seems easily justifiable; at least as justifiable as masking the effective logging of the optimization activity\advice for what are possibly alternative or merely statistically informative Keyed Access Paths.

The query being performed, according to one shown in the OP, seems somewhat inappropriate [or at least unnecessary] activity for a job that is running local to the server; as well, seems more like something that would be run in a QSQSRVR job as a server-mode CLI request for the SQLTables() API, instead of being performed by\within the QDIRSRV job. If a job running local to the server wants to find a file by name, even by the long-file-name, there is a much faster means to request that information than by using the query shown in the OP; the Retrieve Short File Name (QDBRTVSN) API serves that purpose. I would trace the job to determine what code is issuing the query, hoping to infer why and if possibly the software provider [of the identified code] could eliminate the query activity or replace the query with something else.

* The other reason is high number of "not so useful" indexes being
advised and making output of "Index Advisor" not useful. This is not
application issue, this is issue with "Index Advisor".

The intent of the DB2 for i SQL Index Advisor is that the data is always available, on demand, instead of generated only upon request [like on DB2 LUW]; I can not find the document at the moment, but there used to exist a title like /OnDemand Index Advice/ that used to be available from the DB2 for i home page.

Perhaps consider acceding [in part] to the advice, by creating an index that will help reveal to the optimizer, both the cardinality and the selectivity of some of the values; e.g. from the information in the OP, the following INDEX might allow the optimizer to realize the [likely] worthlessness of the recommended keys, per the extremely low cardinality of DBXREL values [just 'Y' and 'N'] and the likely low selectivity of the DBXREL='Y' predicate [typically thousands to one are inclusive]:
CREATE INDEX ALT_QSYS.DBXREL ON QADBXREF (DBXREL)

Perhaps after having those statistics available to the optimizer, the particular advice being logged [and that lacks usefulness] will stop.?

FWiW, two possibly worthwhile topics:
[http://db2fori.blogspot.com/2012/05/take-it-or-leave-it.html]
[http://db2fori.blogspot.com/2013/03/more-advice-is-this-good-or-bad.html]

<<SNIP>>