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
 

 

DB2 Table and accessing programs XREF

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

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Wed Aug 08, 2012 2:02 pm    Post subject: DB2 Table and accessing programs XREF
Reply with quote

Hi,

I've a DB2 Table and would like to know if there is any wayto find out the programs which are accessing the table(including the type of operation like Select,Update,Insert,Delete).

We have around 220 tables and this info will reduce our manual scan.

Thanks in advance
Sree
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 08, 2012 2:23 pm    Post subject:
Reply with quote

Code:
select tcreator, TTNAME, GRANTEE, CONTOKEN, INSERTAUTH, DELETEAUTH, SELECTAUTH ,UPDATEAUTH from sysibm.systabauth
where granteetype = 'P'
and tcreator = ?
and ttname = ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Aug 08, 2012 2:28 pm    Post subject:
Reply with quote

for static sql, suggest you run this once without where clauses
to get an idea of what you actually want to display,
then add the appropriate where clauses.
my system, GRANTEE is program/plan name.

Code:

    SELECT  SUBSTR(GRANTEE,1,15) AS GRANTEE                             
          , SUBSTR(STNAME,1,20) AS STNAME                               
          , CASE DELETEAUTH                                             
              WHEN 'Y' THEN 'D'                                         
              ELSE ' '                                                 
            END AS DEL                                                 
          , CASE INSERTAUTH                                             
              WHEN 'Y' THEN 'I'                                         
              ELSE ' '                                                 
            END AS INS                                                 
          , CASE SELECTAUTH                                             
              WHEN 'Y' THEN 'S'                                         
              ELSE ' '                                                 
            END AS SEL                                                 
          , CASE UPDATEAUTH                                             
              WHEN 'Y' THEN 'U'                                         
              ELSE ' '                                                 
            END AS UPD                                                 
          , GRANTEDTS                                                   
    FROM SYSIBM.SYSTABAUTH                                             
    WHERE COLLID IN ('your collection ids'                                           
                    ,'your collection ids'                                         
                    )                                                   
--      AND SUBSTR(GRANTEE,1,3) NOT IN ('and anything you want to exclude', 'other exclusions')                     
    ORDER BY STNAME                                                     
           , GRANTEE                                                   
           , GRANTEDTS                                                 
    ; 


sorry GuyC, takes me a while to edit my posts and did not see that you have posted already.
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Wed Aug 08, 2012 5:13 pm    Post subject: DB2 Table and accessing programs XREF
Reply with quote

Thanks alot for your replies. It is working as required.
Back to top
View user's profile Send private message
srinut123

New User


Joined: 11 Oct 2005
Posts: 62
Location: India

PostPosted: Wed Aug 08, 2012 5:41 pm    Post subject: DB2 Table and accessing programs XREF
Reply with quote

I've one more question, are these tables updated instantly? I mean when I change a program to remove queries related to a table and bind it, will this catalogue table updated immediately?
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 642
Location: Whitby, ON, Canada

PostPosted: Wed Aug 08, 2012 5:58 pm    Post subject: Re: DB2 Table and accessing programs XREF
Reply with quote

srinut123 wrote:
I've one more question, are these tables updated instantly? I mean when I change a program to remove queries related to a table and bind it, will this catalogue table updated immediately?
Yes it will. The Bind updates several catalog tables, SYSTABAUTH among them.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Aug 08, 2012 6:14 pm    Post subject:
Reply with quote

depending on your system:
a new precompile/bind will generate a new VERSION of the package and thus a new CONTOKEN.
which means you'll get 2 entries in syspackage and systabauth for the same program.
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Cobol list of programs being called biswajit.dattagupta COBOL Programming 5 Tue Nov 01, 2016 2:10 am
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm


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