Index usage

Is there a way to find the following in UDB ?

1. How many times an index is used for a duration ?
2. Which SQL/application is using the index?

if not, is there a different way to find it ?

re: Index usage

One way is to use EXPLAIN on all of the SQL that is being executed and
examine the explain tables. It will tell you what indexes are being used
by what SQL.

If you are interested in usage over a time interval; then you can run
statement traces and correlate the usage counts with the explain data.

If you are using bound packages (not dynamic SQL) then the catalog
tables contain dependency information that will tell you what packages
are dependent on which indexes. This cannot be used to determine usage
over an interval because you have no way to determine which SQL in the
package was actually executed.

Philip Sherman


SKC wrote:[color=blue]
> Is there a way to find the following in UDB ?
>
> 1. How many times an index is used for a duration ?
> 2. Which SQL/application is using the index?
>
> if not, is there a different way to find it ?[/color]

re: Index usage

Query Patroller and it's historical analysis piece will allow you to
do such things potentially.

cshiva@gmail.com (SKC) wrote in message news:<de91becd.0411081134.133c744@posting.google.c om>...[color=blue]
> Is there a way to find the following in UDB ?
>
> 1. How many times an index is used for a duration ?
> 2. Which SQL/application is using the index?
>
> if not, is there a different way to find it ?[/color]