Chuck,
Thank you.
I'll pass your response along to our operations manager.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Monday, January 19, 2015 2:48 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Index Advisor SQL0206 error
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.
--
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.
midrange.com
