On 19-Jan-2015 14:53 -0600, Gary Thompson wrote:
V7R1
I opened Index Advisor from System i Navigator and instead of the
expected list of recommended indexes:
SQL0206 Column &1 not in specified tables.
An error occurred while attempting to initialize the list.
From the text of the msg SQL0206:
Cause: LAST_MTI_USED_FOR_STATS was not found as a column of table *N
in *N and was not found in *N. If the table is *N,
LAST_MTI_USED_FOR_STATS is not a column of any table or view that can
be referenced.
Per the lack of a TABLE or VIEW name, the symptom appears possibly
due to a routine definition [table function (UDTF)] that defines the
TABLE() referenced in a VIEW could be down-level [if possibly a VIEW
itself, the VIEW name would be expected to appear in the replacement
text] is down-level [or the failing-query that references the TABLE
named SYSIXADV data is up-level, but again, the TABLE name would be
expected to appear in the replacement text]. If the error is manifest
from a failed query of the SYSIXADV TABLE, I expect the "*N" replacement
data is likely a defect.
I've not much of an idea of where to start - checking with others
here to see if anyone recognizes the name . . .
A KnowledgeCenter search yields:
IBM i 7.2->Database->Performance and query optimization->Tools->Index
advisor->Display information->System table
<
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/rzajqindexadvisor.htm>
_Database manager indexes advised system table_
"This topic describes the indexes advised system table.
Table 1. SYSIXADV system table
Column Name | System Name | [...]
...
LAST_MTI_USED_FOR_STATS | LASTMTISTA | [...]
...
..."
A web search yields:
<www.ibm.com/support/docview.wss?uid=nas21f380605bde9089f862578ee003c833e>
APAR SE49169 - OSP-DB USE MTIS AS A SOURCE OF STATISTICS
"Abstract:
OSP-DB USE MTIS AS A SOURCE OF STATISTICS
Error Description:
This PTF and its requisites provide two enhancements:
1) Extend the SQL Query Engine to use MTI's as a source for statistics.
This enhancement may result in improved performance of some queries.
2) Include MTI's used for a source of statistics within the index
advisor. This enhancement provides more information when deciding
whether to create permanent indexes for MTI's.
...
New Column definitions:
...
Column name: LAST_MTI_USED_FOR_STATS
System column name: LASTMTISTA
Data type: TIMESTAMP
...
PTFs Available:
R710 SI44492
<
http://www.ibm.com/support/docview.wss?uid=nas35b36a55d692f0e0e862579120066e3e1>
2115
..."
That is a quite old cumulative maintenance level, C2115710, so I
would expect the PTF is already applied.
Presumably that is one of the PTFs whereby there is a program that
the PTF Exit Program runs [asynchronously to the PTF application if
applied *DELAYED] to update some various SQL-provided TABLE and UDTFs to
match the installed PTF level; there exist chances that the program will
not run [successfully], and the program to effect the updates must be
called separately. IIRC that program is called QSQSYSIBM. The PTF
likely will not name that callable program, definitely not as the "exit
program", but the program that is called should likely be a program
shipped in a PTF listed as a pre-requisite; given the PTF SI44492 only
lists co-requisites, perhaps the callable [user domain] program is
listed as one of the program objects included.
midrange.com
