Technical Blog Post
Abstract
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:
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
UID
ibm11141258