I have never worked on a project that did not have some database behind it. DB2, Oracle, Sybase etc all the usual suspects. However, I have always had one or two members in the team who were the db nerds. While I wrote queries, stored procs etc etc when things got difficult with db related issues, they stepped up and I was only too happy to hand it over to them. This is all good. I like to play to the strengths of the team. However, this means I did miss a few low hanging fruits on the way. And here I talk about one such.
So, we delivered this nice project and we were doing a soft launch. As usual our DB2 came under fire. Someone asked a innocuous question. Are all our heavy lifting queries using indexes? Given the number of queries that we have it was not a quick investigation. So, the next question was are all the indexes being used? Or some of them are just hanging around eating resources and not doing anything at all. Surely I knew the answer.
Umm, no, I did not. I should have. This is something that I should have known. Not only for this application after someone asked but for all the applications that I have ever worked with. But the fact is I did not. I suspect strongly that many of the readers are in the same boat. And hence this half rant, half "how-to" note.
You have read the rant part already. Now is the "how-to" part.
Which indexes of your DB2 database are not being used?
select INDSCHEMA, INDNAME, TABSCHEMA, TABNAME, INDEXTYPE, LASTUSED from SYSCAT.INDEXES where INDSCHEMA = 'yourschema' order by LASTUSED ;This should give you a list of all indexes of your database in the order of your last usage. In case they are not used at all the LASTUSED will be '0001-01-01'. There you go. As simple as that.
You will get a much more knowledgeable note on this subject at this link.
That's all for today. If you liked what you read or not liked :) it will help if you leave a comment here.