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>>
midrange.com
