-1

How could I list the DB2 indexes used by a program ?

I of course already have a query to list the tables used by a program using SYSTABAUTH...

But I'd need something similar allowing me to see what indexes are used by a program and what program use a specific index.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
Eric Loriaux
  • 1
  • 1
    Perhaps Db2 for z/OS allows something special, but indexes aren't normally used by a program. They are used by the DB. – Charles Feb 16 '22 at 20:51

1 Answers1

1

Indexes are used by your database, specifically, when commands are being executed. A program may connect to your database and use some indexes, but if there are multiple programs connecting to your database, then it is difficult to find out which program is using which index. Yet, this query is detecting the unused indexes, that is, the indexes that are not used by any program:

SELECT INDSCHEMA, INDNAME, TABNAME 
  FROM SYSCAT.INDEXES
WHERE  LASTUSED = '01/01/0001';

You can adjust LASTUSED to a more recent date, to find indexes that were not used since a given moment that you define.

If you need to find out what indexes are used by a program, then you could do the following:

  • export your database
  • import your database's copy as a different database
  • test your program while being connected to the other database for a (long) while
  • do not connect any other program to the database
  • do not perform any actions on the database apart from the program's actions
  • find the indexes that have been unused since you have imported the copy of your database + 5 minutes (to exclude any index-poking by your import)
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • 1
    Smart way of finding out unused indexes. Of course, there can be processes that run only once a month (or once a year) that could fall off this strategy. – The Impaler Feb 19 '22 at 16:41
  • 1
    @TheImpaler we can set `LASTUSED` to be "old-enough", maybe years old on prod. But, if we separate a copy, then looking into the cron jobs and executing them manually for the sake of the test helps. However, even then we might miss some indexes, as you have correctly pointed out. In that case, if the indexes were important, some performance issues may arise. However, if we know what indexes were removed, then we will also know how we might solve such performance issues. Removing indexes is risky, but we can largely reduce the chance and impact of the risk. – Lajos Arpad Feb 19 '22 at 16:45
  • Just a note. This strategy will also work in DB2 LUW (at least starting on 10.5). – The Impaler Feb 19 '22 at 18:37