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. Good luck!
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our [email protected]/search390>discussion forums.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading