RE: Determining unnecessary indexes

Charles,

We are running OS V5R4. I am running System i Navigator V6R1 and my boss is running V5R4. The fields are visible on both versions of iNav.

It may (or may not) have something to do with the install. Both of us have the whole boat load installed.

Jerry C. Adams
IBM System i Programmer/Analyst
--
B&W Wholesale
office: 615-995-7024
email: jerry@xxxxxxxxxxxxxxx


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, March 26, 2009 1:15 PM
To: Midrange Systems Technical Discussion
Subject: Determining unnecessary indexes

According to Infocenter:

Determining unnecessary indexes

You can easily determine which indexes are being used for query optimization.

Before V5R3, it was difficult to determine unnecessary indexes. Using
the Last Used Date was not dependable, as it was only updated when the
logical file was opened using a native database application (for
example, in an RPG application). Furthermore, it was difficult to find
all the indexes over a physical file. Indexes are created as part of a
keyed physical file, a keyed logical file, a join logical file, an SQL
index, a primary key or unique constraint, or a referential
constraint. However, you can now easily find all indexes and retrieve
statistics on index usage as a result of new V5R3 iSeries(r) Navigator
and i5/OS(r) functionality. To assist you in tuning your performance,
this function now produces statistics on index usage as well as index
usage in a query.

To access this through the iSeries Navigator, navigate to: Database >
Schemas > Tables. Right-click your table and select Show Indexes

However, for showing indexes brings up a window lising the indexes,
but I don't see the following fields describe on the infocenter page

Last Query Use
States the timestamp when the index was last used to retrieve data
for a query.
Last Query Statistic Use
States the timestamp when the index was last used to provide
statistical information.
Query Use Count
Lists the number of instances the index was used in a query.
Query Statistics Use
Lists the number of instances the index was used for statistical
information.

Right Clicking on an index and selecting description gives me a pop-up
with 3 tabs, "Access Path", "Usage", and "Details", none of which have
the fields mentioned above.

Does anybody see these on your version of Client Access?

I'm running CA v5r4 to a v5r4 box.

Thanks!
Charles Wilt