Thanks Rob, good to know about the latest cume.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 20, 2015 7:03 AM
To: Midrange Systems Technical Discussion
Subject: RE: Index Advisor SQL0206 error
You should have them order the latest cume. Both of those are even older than I feared.
I will go under the assumption that you've IPLed at least once since your upgrade to 7.1 (although I may be wrong). Since those say "not applied" I am hoping that ordering and installing the latest cume will fix the issue with why they are not applied. Perhaps it was a prereq/coreq conflict and a newer cume will help to resolve that.
The first IPL after an OS upgrade is often to apply a cume. The first IPL after an upgrade also runs INZSYS. This may not have anything to do with the lack of an applied cume, or the missing column in the view.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 01/20/2015 08:49 AM
Subject: RE: Index Advisor SQL0206 error
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Rob,
5770SS1 SI44492 shows: Superseded
Latest superseding PTF: SI48145
Superseded by: None
WRKPTFGRP PTFGRP(SF99710):
PTF Group Level Status
SF99710 10229 Not installed
SF99710 11116 Not installed
"Not installed" - seems to fit with Chuck's analysis.
I think there are plans to apply, but don't know of a date yet . . .
I defense of ops mgr we are involved in a HUGE national project
which eventually transfers enterprise computation to the cloud,
and are just now getting to our feet after a large acquisition rocked
this particular company.
Thank you.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
rob@xxxxxxxxx
Sent: Tuesday, January 20, 2015 6:21 AM
To: Midrange Systems Technical Discussion
Subject: RE: Index Advisor SQL0206 error
Gary,
Since you have an operations manager I'll assume it's normally taboo for
you to order or apply PTFs. I'm cool with that. But I cannot see where
the management would forbid you from doing a DSPPTF 5770SS1 SI44492 to see
if it is there. Or a WRKPTFGRP PTFGRP(SF99710) to see what cume PTF group
is currently applied.
Have you been on 7.1 for awhile? I would hate to think that they've
recently upgraded to 7.1 and never put on a cume.
I've had SI44492 since
Status date/time . . . . . . . . . . . : 12/06/11 07:49:00
It came out in this cume:
Cum Level........................................... C2115710
Which roughly means it came out in 2012 (C2), Julian date 115, and it's
for 710 of the OS.
God save me from the "if it ain't broke, don't fix it" crowd.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Gary Thompson <gthompson@xxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 01/19/2015 05:17 PM
Subject: RE: Index Advisor SQL0206 error
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
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
<H>
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
