MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Last execution of a Stored Procedure

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Fri Oct 06, 2017 4:35 pm    Post subject: Last execution of a Stored Procedure Reply with quote

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?
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Sun Oct 08, 2017 4:41 am    Post subject: Reply with quote

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           
  ;                                 

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Mon Oct 09, 2017 11:58 am    Post subject: Reply with quote

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;

_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Tue Oct 10, 2017 1:25 am    Post subject: Reply with quote

Nacar9,

Thanks for sharing the queries.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group