How to fined un-unused indexes on DB2 /ZOS

[JAW]

I have some indexes that I don't believe is being used.


What is the best way to determne if this is the case.

[The Boss]

Re: How to fined un-unused indexes on DB2 /ZOS

JAW wrote:

I have some indexes that I don't believe is being used.
>
>
What is the best way to determne if this is the case.

Your first step would be to get a list of all indexes used for _static_
SQL-statements.
For this you can query the SYSIBM.SYSPACKD EP catalog table, to get all
indexes for which there is a package that has a dependency on one of those
indexes, i.e. all indexes used in at least one package's access paths.
For an elimination process, you will be more interested in indexes _not_
used.
You can accomplish this by using your query on SYSPACKDEP as a NOT EXISTS
subquery in the WHERE clause of a SELECT on SYSIBM.SYSINDEX ES

Now you have a list of _possible_ candidates for elimination.
Make sure though that these candidates are not used for some kind of
contraint (RI, uniqueness, ...).

For _dynamic_ SQL there is not such a (relatively) easy way, I'm afraid.
I suppose you could use some performance monitoring tool to help you with
this.
What you also could try is this:
- define a _new_ bufferpool
- choose an index that is a candidate for elimination and alter it in such a
way that it is going to use the newly created bufferpool (if it is going to
be used at all!)
- monitor the usage of the new bufferpool for a couple of days
- depending on the outcome of the bufferpool monitoring, you either drop the
index or alter it back to its original bufferpool
- choose another candidate and repeat ....

Of course this will only work with a fairly limited list of elimination
candidates ...

HTH

--
Jeroen

[JAW]

Re: How to fined un-unused indexes on DB2 /ZOS

On May 22, 1:39 pm, JAW <jwill...@aglre sources.comwrot e:

I have some indexes that I don't believe is being used.
>
What is the best way to determne if this is the case.

Thanks Boss!

I was thinking along these lines.

I guess I need to check plans also for the few left over.