Who is using the DB2 table?
In DB2, is there anyway we can figure out how many people are using the same table?
Your question is an interesting one and really needs to be answered on several different levels. First of all, there is no simple command or SQL statement that you can issue to let you know the number of people who are using a specific table.
But, to be perhaps a little more useful, here are some things to consider:
First, run some queries against the DB2 catalog tables. Check out the
SYSIBM.SYSPLANDEP and SYSIBM.SYSPACKDEP tables. These tables will show you what programs (plans and packages) access which DB2 objects and that includes tables. You can then find out who has the authority to run the plans and packages that access the table(s) you are interested in by querying SYSIBM.SYSPLANAUTH and SYSIBM.SYSPACKAUTH.
Additionally, you should query the SYSIBM.SYSTABAUTH for the table(s) in question to see who has direct authority to insert, update, delete, and select from the table(s). This information will tell you who "can" access the tables but not if they are actually accessing those tables.
Another approach would be to invest in a DB2 SQL performance monitor like BMC Software's APPTUNE
. SQL performance monitors can show you what SQL statements are actually running for both static and dynamic SQL and who is running them. You can then trace the SQL back to the tables being accessed. Make sure that the SQL performance monitor can maintain a history database/file of SQL access so you can go back over time to see what ran and who ran it.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our
This was first published in August 2004