|
View previous topic :: View next topic
|
| Author |
Message |
srinut123
New User
Joined: 11 Oct 2005
Posts: 62
Location: India
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select tcreator, TTNAME, GRANTEE, CONTOKEN, INSERTAUTH, DELETEAUTH, SELECTAUTH ,UPDATEAUTH from sysibm.systabauth
where granteetype = 'P'
and tcreator = ?
and ttname = ? |
|
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
srinut123
New User
Joined: 11 Oct 2005
Posts: 62
Location: India
|
|
|
|
| Thanks alot for your replies. It is working as required. |
|
| Back to top |
|
|
srinut123
New User
Joined: 11 Oct 2005
Posts: 62
Location: India
|
|
|
|
| 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 |
|
|
don.leahy
Active Member
Joined: 06 Jul 2010
Posts: 765
Location: Whitby, ON, Canada
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
|
|
