75 ways to demystify DB2: #4: Tech Tip: How to find when my routine was run last time?
Swati Thorve 270004FTYS Visits (6274)
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:
Hope you can use this to find unused routines in your environment. Please let us know if you have any questions.