In DB2, is there anyway we can figure out how many people are using the same table?

    Requires Free Membership to View

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 .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in August 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: