75 ways to demystify DB2: #4: Tech Tip: How to find when my routine was run last time?

Body

Hello!

Often DBAs asks us to how to find when the routine was run last time? This helps them in finding all the unused SPs, functions and take necessary action on them. Most of the routines create packages. It is easy to find last execution time of the routine by joining three catalog views:

syscat.packages

syscat.routinedep

syscat.routines

You can use the below query:

db2 select rt.routineName as RoutineName, pkg.lastused LastUsed from syscat.routines rt, syscat.routinedep rd, syscat.packages pkg where rt.specificname = rd.routinename and rd.bname = pkg.pkgname

Here is the sample result on my machine:

ROUTINENAME                                  LASTUSED  

--------------------------------------------------------------------------------------------------------------------------------

CURRENTAPPS                                   01/01/0001

SAVE_EXEC_INFO                                01/01/0001

PKGCACHE                                      01/01/0001

CONNECTION                                    01/01/0001

BONUS_INCREASE                                01/01/0001

FOO                                           10/16/2014

Hope you can use this to find unused routines in your environment. Please let us know if you have any questions.

Thanks,

-Swati Thorve