IBM Support

Using iSeries Navigator Fields to Determine If Index Are Not Used

Troubleshooting


Problem

This document provides information on some of the fields in iSeries® Navigator concerning indexes and how to determine if an index was not being used. In addition, it provides additional information on what some of the fields under index description.

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.

This image is of the "Show indexes" screen in iSeries Navigator showung the lasr used date, query use count, days used count, query stastics use count, number of key columns and the key columns.

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:

This shows the iSeries Navigator Index Description. Usage Statistics.  This shows creation date, last used, days used count, days count reset, last change, save and restore as well as experation date, type and partition level identifier.

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:

This image shows index description details including creation date, last used, maximunm wait time, maximum row wait time, maximum row lengrg, sort sequence, format level identifier, and allowed activity,

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.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Component":"DB2 for IBM i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.3;7.2;7.1;6.1.1;6.1;5.4.0","Edition":""},{"Product":{"code":"SSC52E","label":"IBM i 7.1"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSC3X7","label":"IBM i 6.1"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":""},{"Product":{"code":"SSTS2D","label":"IBM i 7.3"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""},{"Product":{"code":"SSC5L9","label":"IBM i 7.2"},"Business Unit":{"code":"BU009","label":"Systems - Cognitive"},"Component":" ","Platform":[{"code":"","label":null}],"Version":"","Edition":""}]

Historical Number

473019249

Document Information

Modified date:
11 November 2019

UID

nas8N1013940