Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

finding the list of programs using db2 tables using rexx

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
i_sivapras

New User


Joined: 04 Aug 2009
Posts: 4
Location: hyderabad

PostPosted: Mon Apr 26, 2010 2:14 pm    Post subject: finding the list of programs using db2 tables using rexx
Reply with quote

Hi,

How can i find list of programs using db2 tables.

for ex : if table A is being used by pgm1,pgm2,pgm3.Pgm1-inserts rows
pgm2- reads the rows pgm3-update the rows.

my output of the rexx should be

tablea pgm1 insert
pgm2 select
pgm2 update

Can any one help me on this
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Apr 26, 2010 3:41 pm    Post subject:
Reply with quote

Solution inside DB2 :

if these are static-sql programs
you can query sysibm.syspackdep and sysibm.syspackstmt

If you have explain info of your programs
you can query PLAN_TABLE and DSN_STATEMNT_TABLE
or if you really want something fancy you can even xquery DSN_QUERY_TABLE

Solution outside DB2 :
scanning & parsing the source libs
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Apr 26, 2010 6:50 pm    Post subject:
Reply with quote

Hi i_sivapras,

What you looking for is CRUD(Create Read Update Delete) Matrix, i think.

I have seen the SQL statements somewhere in web, i don't know whether i have it or not, meanwhile you can search too, if you find post it...

Basically what is does is, it looks at SYSTABAUTH catalog table into (SELECT,INSERT,UPDATE,DELETE)auth columns and gives you the package name.

Thank You,
Sushanth
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1226
Location: Israel

PostPosted: Mon Apr 26, 2010 7:49 pm    Post subject:
Reply with quote

Sushanth,
I am not a DB2 specialist, so I took the time to Google for 'CRUD Matrix'.
It took me another 25 seconds to do a SELECT on table SYSTABAUTH.

And another split second to understand that your answer is valueless. icon_sad.gif
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Apr 26, 2010 8:51 pm    Post subject:
Reply with quote

Hello Marso,

Quote:
I am not a DB2 specialist

Me too, i am just a beginner.......

I did a little search and i couldn't find it, since the CRUD had spaces in-between. After that, did some browsing and found it and executed it.

Code:
--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           
---------+---------+---------+---------+---------+---------+---------+-


Let me know if this does the trick....

Thanks,
Sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts REXX cdoe to purge a JOB from spool a... Sulabh Agrawal CLIST & REXX 11 Thu Mar 09, 2017 11:36 am
No new posts SPUFI -- Joining 3 tables – data in... Sysaron DB2 2 Wed Mar 08, 2017 4:18 am
No new posts To know activities on db2 tables ashek15 DB2 4 Fri Mar 03, 2017 11:23 pm
No new posts Overriding PS data in rexx Shaheen Shaik CLIST & REXX 8 Fri Mar 03, 2017 5:08 pm
No new posts Why myself doing Package Bind always ... Susanta DB2 5 Thu Mar 02, 2017 10:47 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us