Historic data on index utilisation?

[Bruce Pullen]

DB2 7.2 on AIX 5.2.

I'm looking to remove redundant indexes.

The GET SNAPSHOT FOR ALL command does not appear to include historic
data for index utilisation. Can anyone give me any tips outside using
EXPLAIN on each bit of SQL code?

Thank you, and a healthy 2004.

[Larry]

Have you tried running the index advisor (not sure if it was there in
V7): "db2advis"?

Larry Edelstein

Bruce Pullen wrote:

DB2 7.2 on AIX 5.2.

I'm looking to remove redundant indexes.

The GET SNAPSHOT FOR ALL command does not appear to include historic
data for index utilisation. Can anyone give me any tips outside using
EXPLAIN on each bit of SQL code?

Thank you, and a healthy 2004.

[Jean-Marc Blaise]

Bruce,

Except the explain and use of the db2advis utility, there is no way out.
Yes, I'd like to have a SNAPSHOT FOR INDEXES in a future version of DB2 UDB
to check index reads; Oracle has implemented an analyze mode for indexes in
version 9i. That would help and avoid this fastidious task to be sure to get
the complete workload.

Regards,

Jean-Marc

"Bruce Pullen" <br**********@hotmail.com> a écrit dans le message de
news:9d**************************@posting.google.c om...

DB2 7.2 on AIX 5.2.

I'm looking to remove redundant indexes.

The GET SNAPSHOT FOR ALL command does not appear to include historic
data for index utilisation. Can anyone give me any tips outside using
EXPLAIN on each bit of SQL code?

Thank you, and a healthy 2004.

[Bob [IBM]]

FWIW ... I believe the V8.1 Query Patroller product will provide this type of information? Maybe others more knowledgeable in the product can comment ... I believe that with QP V8.1 (and yes I know the poster was using V7.2 - do not know if it is available in V7.2 or not):


-Using Historical Analysis feature, we can find the following:

- queries run by users

- what tables are accessed and how many times
- what tables are not touched by queries
- what indexes are accessed any how many times
- what indexes are not touched by queries

- submitter information

--

Bob
Engagement Specialist - IBM Software Services for Data Management
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position - ask my cat!]
"Jean-Marc Blaise" <no****@nowhere.com> wrote in message news:bs**********@news-reader2.wanadoo.fr...
Bruce,

Except the explain and use of the db2advis utility, there is no way out.
Yes, I'd like to have a SNAPSHOT FOR INDEXES in a future version of DB2 UDB
to check index reads; Oracle has implemented an analyze mode for indexes in
version 9i. That would help and avoid this fastidious task to be sure to get
the complete workload.

Regards,

Jean-Marc

"Bruce Pullen" <br**********@hotmail.com> a écrit dans le message de
news:9d**************************@posting.google.c om...

DB2 7.2 on AIX 5.2.

I'm looking to remove redundant indexes.

The GET SNAPSHOT FOR ALL command does not appear to include historic
data for index utilisation. Can anyone give me any tips outside using
EXPLAIN on each bit of SQL code?

Thank you, and a healthy 2004.