Index Utilization at Database level

[reachsamdurai]

Hello, I'm looking for a method to check the utilization of index by
any SQL (Static and Dynamic) at the database level instead of
generating the access plan for each SQL.

I have inherited a database where index occupies more space than the
data and most of the indexes are looking redundant to me hence I would
like to check each index utilization and drop the same if it is not
used.

Env: DB2 UDB 8.2 /AIX 5.3
Thanks,
Sam

[Mehmet Baserdem]

Sam,

I know it is not what you are looking for, but may want to start with
catalog tables syscat.packagedep and syscat.statements.They will help
you to identify embedded sql stmts and indexes in the packages.

Regards,

Mehmet Baserdem

[Larry]

re***********@gmail.com wrote:

Hello, I'm looking for a method to check the utilization of index by
any SQL (Static and Dynamic) at the database level instead of
generating the access plan for each SQL.

I have inherited a database where index occupies more space than the
data and most of the indexes are looking redundant to me hence I would
like to check each index utilization and drop the same if it is not
used.

Env: DB2 UDB 8.2 /AIX 5.3
Thanks,
Sam

You should take a look at DB2 Query Patroller:

http://www-306.ibm.com/software/data...uerypatroller/

Larry Edelstein

[rAinDeEr]

hi Sam,

You can use the index advisor to find out the unused indexes and which
will give the necessary index you should have in your database.

1.) First set up the following tables
EXPLAIN_ARGUMENT
EXPLAIN_INSTANCE
EXPLAIN_OBJECT
EXPLAIN_OPERATOR
EXPLAIN_PREDICATE
EXPLAIN_STATEMENT
EXPLAIN_STREAM

There is a script which will automatically create these tables for
you..I forgot the location..will let you know ASAP.

2.) You should store all your sql in a file(query.in for example).

3.) run command

db2advis -d database_name -i query.in -t 5 -n schema_name

which will show some thing like below ::

total disk space needed for initial set [ 0.405] MB
total disk space constrained to [ 9.683] MB
Trying variations of the solution set.
Optimization finished.
37 indexes in current solution
[406.4958] timerons (without recommendations)
[ 36.6679] timerons (with current solution)
[90.98%] improvement
-- LIST OF RECOMMENDED INDEXES
-- ===========================
-- index[1], 0.009MB
CREATE UNIQUE INDEX "schemaname "."IDX603090642270000" ON
"schemaname "."T_CNTR " ("CNTR_CAT_CDE" ASC, "CNTR_SUB_CAT_CDE" ASC)
INCLUDE ("PROD_TYP_CDE") ALLOW REVERSE SCANS ;
COMMIT WORK ;
RUNSTATS ON TABLE "schemaname "."T_CNTR" FOR INDEX "DB2GRA
"."IDX603090642270000" ;
COMMIT WORK

-- RECOMMENDED EXISTING INDEXES
-- ============================
-- RUNSTATS ON TABLE "schemaname "."tablename" FOR INDEX "schemaname
"."PK_CNTRY" ;
-- COMMIT WORK ;
-- UNUSED EXISTING INDEXES
-- ============================
-- DROP INDEX "WDI "."IO1";
-- DROP INDEX "WDI "."IO2";

[Mehmet Baserdem]

rAinDeEr,

Did you mean to say "db2 -tf EXPLAIN.DDL"

The "query.in" file will not cover all statements/scenarios. Isn't Sam
still need historical usage data for indexes?

Is there any way to log/archieve all sql stmts ever issued to database
server?

regards,

Mehmet Baserdem

[rAinDeEr]

Yes Mehmet,

I was talking about EXPLAIN.ddl which will create the tables. When we
run the db2advis it will recommend the new indexes and the utilisation
of the indexes and think that was exactly what Sam needed.

Also the redundant indexes are found by the advisor and is recommended
to DROP.

You can have the all the SQL's stored the file. I am not sure of a way
to capture dynamic SQL's.

You need db2audit to log all SQL submitted - The audit facility acts at
an instance level, recording all instance level activities and database
level activities.

thanks,
rAinDeEr

[ChrisC]

rAinDeEr wrote:

You can have the all the SQL's stored the file. I am not sure of a way
to capture dynamic SQL's.

If you have a new enough version of DB2 (I think 8.2), then you can use
the -g option to pull the queries from a dynamic SQL snapshot.
Alternatively, you can also use the -qp option to pull queries from
Query Patroller (or, apparently, use the GUI to gather a more
interesting range of data form the Query Patroller). These options are
documented in the infocenter. Unfortunately, I'm not currently running
a new enough version of DB2 to try either of these options out myself.

-ChrisC

[reachsamdurai]

Thanks Chris and Reindeer. I would look at the options suggested by
you.

Sam..