are these indexes being used?

itsonlyme44 · #1 (**permalink**) 05-27-09, 13:57

DB2 9.5 LUW

Is there a catalog table or something that I can query to find out which indexes are being utilized?

We have spent the last year or so building up our Data Mart and in looking at all of the indexes that we have deployed - I'm thinking we are not utilizing all of them.

Is there any way to report on this?

Cougar8000 · #2 (**permalink**) 05-27-09, 13:59

It was just talked about it last week I think.

db2pd is the best tool to hunt for unused indexes:
Code:

db2pd -db sample -tcbstats index

Look for the "TCB Index Stats:" header and the "Scans" column.
This will tell you how many times DB2 used that index. If the counter is ZERO, DB2 did not need that index at all so.......
Be ware:
1 - when a database stops and starts again all the counters are reset to ZERO
2 - beware of FK contraints. The index might be required as FK.

You do need SYSADM access

itsonlyme44 · #3 (**permalink**) 05-27-09, 15:35

I know this is going to sound stupid.. but what is the correct syntax to run this from COMMAND EDITOR?

I try and run it like this:

db2pd -db PRODDM -tcbstats index

and I get this:

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "db2pd -db" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
SQLSTATE=42601

Cougar8000 · #4 (**permalink**) 05-27-09, 15:40

Try downloading some manuals

IBM - DB2 9.1 for Linux, UNIX and Windows manuals

itsonlyme44 · #5 (**permalink**) 05-27-09, 15:42

Thank you!

itsonlyme44 · #6 (**permalink**) 06-04-09, 15:16

Can anyone tell me how to find the tableid? I need it to run DB2PD like this:

db2pd -db PRODDM -tcbstats index tbspaceid = 3 tableid = xxx

n_i · #7 (**permalink**) 06-04-09, 15:26

Quote:

Originally Posted by itsonlyme44

Can anyone tell me how to find the tableid?

It is in SYSCAT.TABLES, obviously.

itsonlyme44 · #8 (**permalink**) 06-04-09, 16:10

Thank you!!! I missed that... I work more with SQL Server and was looking for an object table...

anyway.. can anyone help me interpret the output from this:

Code:

>db2pd -db PRODDM -tcbstats index  > c:\PRODDM_Stats.txt

Code:

TCB Index Stats:
Address    TableName          IID   EmpPgDel   RootSplits BndrySplts PseuEmptPg Scans      KeyUpdates InclUpdats NonBndSpts PgAllocs   Merges     PseuDels   DelClean   IntNodSpl  0          0          0          0          0          0          0          0          0          0          0          0          0          
0x59456DA8 T9DAT              2     0          0          0          0          16         0          0          0          0          0          0          0          0          
0x59456DA8 T9DAT              1     0          0          0          0          9          0          0          0          0          0          0          0          0          
0x59458028 T9PH               1     0          0          0          0          0          0          0          0          0          0          0          0          0          
0x59455728 T9PSC              2     0          0          0          0          26         0          0          0          0          0          0          0          0          
0x59455728 T9PSC              1     0          0          0          0          1          0          0          0          0          0          0          0          0          
0x5945EC28 T9PSR              2     0          0          0          0          2          0          0          0          0          0          0          0          0          
0x5945EC28 T9PSR              1     0          0          0          0          0          0          0          0          0          0          0          0          0

For instance I have mulitiple indexes on the tables listed.. so how do I know which indexes are being used?

Cougar8000 · #9 (**permalink**) 06-04-09, 16:22

this information is only current from the last time instance was cycled.

As I understand "scans" column tells you number of times index was used. You might not want to base your decision based on one output. I would collect the info for at least a couple of month and decide then.

n_i · #10 (**permalink**) 06-04-09, 18:42

Quote:

Originally Posted by Cougar8000

this information is only current from the last time instance was cycled.

I'd say it has accumulated since the time of the database activation, which may or may not coincide with the start of the instance.

itsonlyme44 · #11 (**permalink**) 06-05-09, 05:41

I see now.. the IID is the index ID. lots of crosschecking involved to figure out which index is which (we have a couple hundred tables in our datamart with up to 5 indexes on each table) but the information on index useage is well worth the trouble.. thank you for your help

Internet Services

Web Development

Digital Marketing

Consumer Tech