question on index usage

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

paul - 08 Feb 2006 13:20 GMT

Hi,
is there any way in db2 v8.2.3 (fp10) on aix 5.3 to know if indexes on table
are used.
The only method i know is to create an event monitor on statement and then
pass this worload to the design advisor. Is there a faster way.
Again, i some some queries that worked better on db2 v8.1. I migrated this
into db2 v8.2 and those queries take table fullscan now instead of hash
join.
I guess that the optimser changed between these two versions but not in the
right direction.
I've made a full db reorg + full runstats + rebind.

thx

Reply to this Message

Bob [IBM] - 08 Feb 2006 14:38 GMT

The db2pd command/tool should help you here.

db2pd -d <dbname> -tcbstats index

The output will give you activity on the indices from the DB2 point-of-view
(page deletes, etc.) ... but at least it will tell you which indices are
never used over time.

Bob

> Hi,
> is there any way in db2 v8.2.3 (fp10) on aix 5.3 to know if indexes on table
[quoted text clipped - 9 lines]
>
> thx

Reply to this Message

paul - 08 Feb 2006 15:44 GMT

thx that sould work fine

> The db2pd command/tool should help you here.
>
[quoted text clipped - 21 lines]
> >
> > thx

Reply to this Message

Larry - 08 Feb 2006 22:08 GMT

> Hi,
> is there any way in db2 v8.2.3 (fp10) on aix 5.3 to know if indexes on table
[quoted text clipped - 9 lines]
>
> thx

Query Patroller will also provide this type of functionality, but it is
an extra cost feature.

Larry Edelstein

Reply to this Message