Hello Friends,

To analyze which indexes are being used, I need to check how many index scans were run on a given index. That means how many times an index had been used in past 24 hours.

Can someone help me find data for an index and maybe if possible about a table too.

Regards

Which DB2 version and OS?

Andy

DB2v9.5.5 on Win2K3

Look here:

are these indexes being used?

Andy

Alright,

So db2pd can be run periodically.

Thanks for help

As much as your heart desires but I would only run it before deactivating db/cycling the instance. which ever comes first. That is assuming you do it on a regular intervals for house cleaning. If not, you are going to see data there from the last time one of those events took place.

Well,
I am writing a script for daily checks. At the begining of each day. I guess I should keep a record for about 7 days or so; to review daily ; for a look on the trend.

Regards

You are going to be wasting your and others time. This is not something you check on a daily or even weekly basis. I would venture to say that this is something that you want to check on a quarterly basis unless you are creating new indexes and implementing new projects on a daily basis.

Agree,

I just had a discussion; we are not going to set up a daily job for this. May be once a while , or when my manager wants to have a look on details.

Regards for good suggestion

I have decided to take another look at it and possibly finally write something up that will take a manual work out of the picture, if it all possible.

I would be curious to compare the notes and see how you are going about it and maybe put a collective together to get this done.

What I have found out thus far is for some unknown reason IBM did not extended fields for tbname, schema on the output and if you are on a 9 what ever your tables most likely been extended past 18 char point.

This makes it a bit tricky to get a correct info out. In the index section it only gives you a chopped tbname and an IID of the index. So, to identify the correct index you have to cross match hex value from an index section to a hex value on the table section to get a correct tableID. After that match it with a correct tbname and only then go get a correct indname. They sure do not make it easy on you.

I think I will pipe the output to a file. From there I would slice and dice that file into a two sections: table and index files. From there load that data into tables and then write SQL to get what I really need.

What do you think?

I usually do the same trick, output to textfile and process it for presentation. I will appreciate when you will do that.

My surprise is that why IBM has not provided a view like may other useful views. Is there any place where we can suggest IBM for future versions.

I can do a lot easy scripting in windows native .BAT scripts and also VBScript system scripts.

Regards

It is available in V9.7:

IBM DB2 9.7 for Linux, UNIX and Windows Information Center

Andy

hmm, I guess I can go back to sleep ya'll good night

Thanks Andy

sleep tight