Using iSeries Navigator Fields to Determine If Index Are Not Used

Resolving The Problem

Q1: What fields in iSeries® Navigator can a user check to determine indexes that are not being used?

A1: Under Schemas, specific schema, Tables, right-click on a specific table, and select 'Show Indexes'. There are several fields that can be checked to determine this.



If an index is not being used, the following 3 dates are or could be blank:

Last Query Use
Last Query Statistics Use
Last Used Date

and the following count fields are or could be 0:

Query Use Count
Query Statistics Use Count
Days Used Count

The 'Last Used Date' and the 'Days Used Count' show if an index is being accessed natively rather than using SQL.

Q2: What are the meanings and differences of the 'Last used' and 'Last change' fields in an iSeries Navigator index description?

A2: In iSeries Navigator under Schemas, specific schema, Indexes, right-click on a specific index, and select Description.

Then, select 'Usage' tab. There are fields:
Last used:
Last change:

Then, select the Details tab. There is a field:
Last used:
(This is the one that is most confusing because it is really a Last change field).

Following are the screen samples:

Usage:



Last used:
Meaning - The date the member was last used.
The 'Last used' date is taken from: QUSRMBRD format: MBRD0200 Date_Lst_Used field.
This is a member level last used.

Last change: 10/12/07 5:18:50 PM
Meaning - The date the member was last changed. This field is a time stamp. If the member has no last changed date, the field is blank. The Last change' date is taken from: QUSRMBRD format: MBRD0100 Change_Date field - last source change or table refresh date and time.
This is a member level last change.

Details:



Last used: 10/12/07 5:18:50 PM
Meaning - The date the object was last changed. This field is a timestamp. If the object has no last changed date the field is blank. The Last used date is taken from: QUSROBJD format OBJD0100. In the QSYSINC/H include file it is the Change_Date_Time value. This is an object level last change.