Last execution of a Stored Procedure
Select messages from
# through # FAQ
[/[Print]\]

MVSFORUMS.com -> Database

#1: Last execution of a Stored Procedure Author: NASCAR9Location: California PostPosted: Fri Oct 06, 2017 4:35 pm
    —
I searched and nothing came up, it could be my search skills Embarassed

Is there a query that can tell me the last time a stored procedure executed?

#2:  Author: kolusuLocation: San Jose PostPosted: Sun Oct 08, 2017 4:41 am
    —
NASCAR9,

Try this untested sql and see if you can get the desired results.

Code:

SELECT A.SCHEMA                     
      ,A.NAME                       
      ,C.LASTUSED               
   
 FROM SYSIBM.SYSROUTINES    A       
     ,SYSIBM.SYSPACKDEP     B       
     ,SYSIBM.SYSPACKAGE     C       

WHERE A.NAME         =  B.DNAME     
  AND B.BNAME        =  C.NAME     
  AND C.LASTUSED     > '0001-01-01'
ORDER BY C.LASTUSED DESC           
  ;                                 

#3:  Author: NASCAR9Location: California PostPosted: Mon Oct 09, 2017 11:58 am
    —
Thanks kolusu,

The above query did not give me the complete list. It did give me the column I need to look at( LASTUSED).
Below are two queries I built. The first one gives all the detail about Objects in the database. The second is a unique list of stored procedures. Both queries have Where block data commented out, uncomment and fill in your target specific object.

I'm sure these can be more refined, I may have made assumptions that aren't 100%.

Code:

---Stored Object plus Tables and more (Vertical)

 SELECT
        B.BQUALIFIER,
        B.BNAME,
        TRIM(A.COLLID) || '.' || TRIM(A.NAME),
        A.OWNER,
        A.VERSION,
        A.VALID, 
        A.OPERATIVE,
        A.TIMESTAMP,
        A.BINDTIME, 
        A.PKSIZE, 
        A.ISOLATION,
        A.LASTUSED,
        CASE (B.BTYPE)
           WHEN 'A'   THEN 'Alias'   
           WHEN 'E'   THEN 'INSTEAD OF trigger'
           WHEN 'F'   THEN 'User-defined function or cast function'
           WHEN 'H'   THEN 'Global variable'
           WHEN 'G'   THEN 'Global temporary table'
           WHEN 'I'   THEN 'Index'
           WHEN 'M'   THEN 'Materialized query table'
           WHEN 'O'   THEN 'Stored procedure'
           WHEN 'P'   THEN
                  'Partitioned table space if it is defined as LARGE or with the DSSIZE parm'
           WHEN 'Q'   THEN 'Sequence object'
           WHEN 'R'   THEN 'Table space'
           WHEN 'S'   THEN 'Synonym'
           WHEN 'T'   THEN 'Table'
           WHEN 'U'   THEN 'Distinct type'
           WHEN 'V'   THEN 'View'
           WHEN 'W'   THEN 'SYSTEM_TIME period'
           WHEN 'Z'   THEN 'BUSINESS_TIME period'
           WHEN '0'   THEN 'Sequence alias'
        END   AS TYPE       
 FROM SYSIBM.SYSPACKAGE A,
      SYSIBM.SYSPACKDEP B
 WHERE (A.COLLID=B.DCOLLID
    AND A.NAME=B.DNAME
--   AND A.COLLID='XXXXXL'   ---YOUR COLLECTION ID
--   AND B.BTYPE='T'              --- TABLE only
   )
   AND LENGTH(A.NAME) > 8   --- Try and weed out IBM System Stuff
   AND A.LASTUSED     > '0001-01-01'
   FOR FETCH ONLY WITH UR;
   
   
--- JUST PROCEDURE   

 SELECT
        TRIM(A.COLLID) || '.' || TRIM(A.NAME),
        A.VERSION,
        A.VALID, 
        A.OPERATIVE,     
        A.LASTUSED       
 FROM SYSIBM.SYSPACKAGE A,
      SYSIBM.SYSPACKDEP B
 WHERE (A.COLLID=B.DCOLLID
    AND A.NAME=B.DNAME
--   AND A.COLLID='XXXXXL'   ---YOUR COLLECTION ID
    AND B.BTYPE='T'               --- TABLE only
   )
   AND LENGTH(A.NAME) > 8   --- Try and weed out IBM System Stuff
   AND A.LASTUSED     > '0001-01-01'
GROUP BY
      TRIM(A.COLLID) || '.' || TRIM(A.NAME),
        A.VERSION,
        A.VALID, 
        A.OPERATIVE,     
        A.LASTUSED       
   FOR FETCH ONLY WITH UR;

#4:  Author: kolusuLocation: San Jose PostPosted: Tue Oct 10, 2017 1:25 am
    —
Nacar9,

Thanks for sharing the queries.



MVSFORUMS.com -> Database


output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1

Powered by phpBB © 2001, 2005 phpBB Group