The last time an index was used

[Former Member]

Hi to all,

I would like to know, if there is a way of knowing the last time an index was used, or what indexes have never been used.

Regards

Eduardo Barrios

Compañía Cervecera de Canarias S.A.

Tfno. 922 568 246

Fax. 922 670 600

Correo ebarrios@ccc.es

[christian_bartels]

Hi Eduardo,

reading through the updates from Thomas and Volker, I was wondering if there was a bug in i5/OS that causes the "Last used date" information in the file description not to be updated properly for indexes. If you have the plan cache dump or database monitor active (i.e. quite current data in ST04), you could do the following to find out if indexes have been used by the SAP system without a proper update in the file description:

1. List all indexes into a temporary output file:

DSPFD FILE(R3<sid>DATA/ALL) TYPE(MBR) OUTPUT(*OUTFILE)

FILEATR(*LF) OUTFILE(QTEMP/DSPFD)

2. Compare output with database monitor data using STRSQL or SQLUTIL:

SELECT T1.MBFILE, T1.MBUDAT, T2.QQTIME, T2.QQLIFN

FROM QTEMP/DSPFD T1, R3<sid>DATA/SQ3001_DB4 T2,

R3<sid>DATA/SYSINDEXES T3

WHERE T1.MBFILE = T3.SYSTEM_INDEX_NAME

AND T3.INDEX_NAME = T2.QQLIFN

ORDER BY T1.MBUDAT

If you find indexes, where the "Last Used Date" is smaller than the QQTIME value, you know that the

file statistics are not updated properly. You could then open a PMR at IBM for that.

On one of our internal systems I did not see a discrepancy, so it seems to me that the information is correct. Volker's experiences could be explained by the fact that indexes are not just used to implement SQL statements, but also for table statistics. I assume that the "Last used date" is not updated when the index was only used to obtain table statistics. With incomplete statistics, the optimizer may choose a different join order or access method, which can result in poor performance. So there is some risk involved when dropping an index that has a very old "Last used date", and you can only decide it by trying (with care!).

Kind regards,

Christian Bartels.

[Former Member]

Hi Thomas, hi Volker,

My goal is customer´s indexes, not SAP indexes, because we have some very long, but as Volker says, DSPFD and ST04 are not reliable, it would be better, do nothing.

Thanks for the help.

Eduardo

[Former Member]

Hi Eduardo,

I tried this once and found out, that a few indexes were not used anymore (not in ST04 and not in DSPFD). ST04 is available in 4.6C as well, if you turn on the DB-Mon. But, you should ONLY turn on the very new DB-Monitor on V5R4 as described in note 1083218, because this one doesn't eat up ressources.

Then I removed 1-2 indexes on large tables and the performance got really a horror ;-(

=> I do not try to remove indexes any more if not REALLY NECESSARY - especially not SAP delivered indexes ...

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.de - http://www.4soi.de - http://www.easymarketplace.de

[Former Member]

Hi Thomas,

Yes, I know ST04, and I think that there it would have to have some information about not used indexes, but as far as I know in 46.C it does not exist. I was thinking if perhaps on the OS/400 side, there is something.

Thanks

Regards

Eduardo

[thomas_obermeier]

Hi Eduardo,

Ever tried to use DSPFD? One piece of information you get there is

Last used date

Days used count

HTH,

Thomas

[Former Member]

Hi Eduardo,

the only way i know is to use Tx ST04 (DB2/400 Monitor) , Detail Menu.

Indexes advised , Index used etc.

Grtz

Thomas