DB2 Stored Procedure Last Used Date

Ever wonder when was a stored procedure last used/invoked in DB2 LUW? Knowing this may be helpful in determining whether a stored procedure is still in use or how recently it was used. Run this query to find this information.

SELECT PR.PROCSCHEMA
      ,PR.PROCNAME
      ,PK.LASTUSED
  FROM SYSCAT.PROCEDURES PR
      ,SYSCAT.ROUTINEDEP RD
      ,SYSCAT.PACKAGES   PK
 WHERE PR.SPECIFICNAME =  RD.ROUTINENAME
   AND RD.BNAME        =  PK.PKGNAME
   AND PR.PROCSCHEMA  <> 'SYSIBMADM'
   AND PK.LASTUSED     > '01/01/0001'
 ORDER BY PK.LASTUSED DESC
  WITH UR;

Remember that, you can use this query only from DB2 LUW V9.7 onwards.

DB2 DIAGNOSE DISPLAY MEPL

DIAGNOSE is a DB2 for z/OS utility that, as the name suggests, is used in diagnosing problems. It generates information that is useful in diagnosing problems. But you should use this utility only under the direction of IBM Software Support staff, except for DIAGNOSE DISPLAY MEPL.

You can use the DISPLAY MEPL even without contacting IBM in the following scenario.

First of all, MEPL is an acronym for Module Entry Point List (MEPL). DIAGNOSE DISPLAY MEPL dumps module entry point lists to SYSPRINT.

Recently, we had an issue with a query after applying the maintenance RSU1304. I opened a SR (Service Request) with IBM and the support staff found a PTF in PE status. It took about 2 months to find a fix and eventually when I applied a PTF to fix the PTF in PE, it didn't work. The query still had the same problem.

IBM asked me to run this utility to find if the PTF is in fact applied to the subsystem that we were testing the query. The SYSPRINT from this utility didn't show the PTF. That's when I realized that the PTF was applied to the maintenance library, but the load module was not copied to the production run time library. After copying the load module from maintenance library to run time library, the query ran good.

Usually I do search the SMPPTS library to see if PTF that was applied recently is found, but that's not foolproof. This method of running the DIAGNOSE DISPLAY MEPL utility looks foolproof. Lesson learnt !!

Here is a sample job to run this utility:

//jobcard
//JOBLIB   DD  DISP=SHR,DSN=SYS2.DB2.DBxx.SDSNLOAD
//STEP0010 EXEC PGM=DSNUTILB,PARM='DBxx,MEPL',REGION=4M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  DIAGNOSE DISPLAY MEPL
/*

Thanks for stopping by.