Retrive Programs related to the DB2 tables.



JES, JES2, JCL utilities, IDCAMS, Compile & Run JCLs, PROCs etc...

Retrive Programs related to the DB2 tables.

Postby Surabhi » Wed Aug 24, 2016 5:32 pm

Hi all,

I am curious to know whether is it poossible to get the list of programs whish are using DB2 tables in a PS file without using any cobol code.
On some search i did find some SQL statements which do fetch the pgms of related table just wondering if can get all these in a PS file with JCL.

--TO GET A C R U D MATRIX FOR A DB2 PLAN OR PACKAGE
--                                                              
 SELECT SUBSTR(TCREATOR,1,10) AS CREATOR              
      , SUBSTR(TTNAME,1,10)   AS NAME                  
      , SUBSTR(GRANTEE,1,10)  AS PROGRAM              
      , CASE WHEN INSERTAUTH = 'Y' THEN 'C'                      
             ELSE '-'                                            
        END      AS C                                            
      , CASE WHEN SELECTAUTH = 'Y' THEN 'R'                      
             ELSE '-'                                            
        END      AS R                                        
      , CASE WHEN UPDATEAUTH = 'Y' THEN 'U'                  
             ELSE '-'                                        
        END      AS U                                        
      , CASE WHEN DELETEAUTH = 'Y' THEN 'D'                  
             ELSE '-'                                        
        END      AS D                                        
      , CASE WHEN COLLID = ' ' THEN '** PLAN **'              
             ELSE COLLID                                      
        END      AS "PLAN/COLLECTION"                        
      , CASE WHEN CONTOKEN = ' ' THEN CONTOKEN                
             ELSE HEX(CONTOKEN)                              
       END      AS TOKEN                                      
   FROM SYSIBM.SYSTABAUTH            
  WHERE GRANTEETYPE = 'P'            
  AND TCREATOR = 'SHANDB'            
 ;                                                                  



---------+---------+---------+---------+---------+---------+---------+-
 CREATOR     NAME        PROGRAM     C  R  U  D  PLAN/COLLECTION        
 ---------+---------+---------+---------+---------+---------+---------+-
 SHANDB      EMPLOYEE    BOBDBRM     -  R  -  D  ** PLAN **              
 SHANDB      EMPLOYEE    DB2SEL      C  -  -  -  BOBC                    
 SHANDB      EMPLOYEE    DB2DEL      -  -  -  D  BOBC                    
 SHANDB      EMPLOYEE    DB2INS      -  R  -  -  BOBC                    
 SHANDB      EMPLOYEE    DB2CURSO    -  R  -  -  BOBC                    
 SHANDB      EMPLOYEE    MULTISEL    -  R  -  -  BOBC                    
 SHANDB      EMPLOYEE    DB2CURFU    -  R  -  -  BOBC                    
 SHANDB      EMP         KMKIMADB    C  -  -  -  INVF                    
 SHANDB      EMP         BMKIRADB    -  R  -  -  KIRA                    
 SHANDB      EMP         TRIGGER3    -  R  -  -  SHANDB                  
 SHANDB      EMPLOYEE    DB2MULSE    -  R  -  -  TEST                    
 DSNE610I NUMBER OF ROWS DISPLAYED IS 11                                
 DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100            
 ---------+---------+---------+---------+---------+---------+---------+-


Any input will be very helpful.
Thank you for your help in advance.

CODE' D FOR READABILITY
Surabhi
 
Posts: 49
Joined: Thu Sep 10, 2015 7:51 pm
Has thanked: 4 times
Been thanked: 0 time

Re: Retrive Programs related to the DB2 tables.

Postby Akatsukami » Wed Aug 24, 2016 7:04 pm

No. JCL does not do anything except run programs and establish their environments. Now, if you had asked, "Are there utilities I can use to execute this SQL?", you might have gotten a different answer.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times


Return to JCL

 


  • Related topics
    Replies
    Views
    Last post