iSeries, DB2 Need to know which tables are being accessed by a Process/User and how often.

Posted on 2011-10-12
Medium Priority
Last Modified: 2012-05-12
Hello EE,

I have an external system that is linked to DB2 and reads data from tables and views.  This is a broad external system.  I need to know which tables and views are being read from and how often.  Can you tell me what system table this is logged in?  I'll be querying this table via a linked query from SQL Server.

Question by:Lawrence Barnes
LVL 18

Accepted Solution

Dave Ford earned 1000 total points
ID: 36957269

Greetings, lvbarnes!

Unfortunately, database access is not logged in a normal "table" (at least not in the way we database people understand it). The best way to access that information is through journals (as long as journaling is turned on).

You could have someone with native access to the box do a Display-Journal command (DspJrn) to an outfile. That would create a database file with all the (very cryptic) information that you'll ever need.

This will take a significant amount of expertise and research. Good luck.

LVL 37

Assisted Solution

momi_sabag earned 1000 total points
ID: 36957273
select queries are not logged any where
insert / update / delete are logged in the db2 log, not a system table but a file

the simplest solution in my opinion would actually be to monitor it on the SQL Server side
it has much more friendly tools for that

Author Closing Comment

by:Lawrence Barnes
ID: 36957729
Thank you
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 27

Expert Comment

ID: 36957760
I have an external system that is linked to DB2 and reads data from tables and views.

Can you supply any information about this "external system"? Mainframe? Windows? UNIX? Especially, do you know what access method it uses? A mainframe might use DDM/DRDA. A Windows system might use ODBC. Other methods are possible and the method might indicate a potential logging method.

Can you tell me what system table this is logged in?

Most probably there is no "table" that is currently used for any logging of database access. If general system facilities are used, logging is done into an 'object' (probably a journal object). To query the object, you would use the appropriate object methods.

As DaveSlash mentioned, the DSPJRN command is a potential way to invoke a method that can convert journal data into a database format. However, by itself, it doesn't exactly create records that you might want to query with SQL Server. The records from DSPJRN tend more to give a kind of meta-data about each transaction -- name of the user, the table that was accessed, timestamp, etc. It does also include the transaction itself, but it's in a raw format that may need some preparation for SQL Server to make good sense of it. With a little preparation, the DSPJRN command can be run in a way that gives very detailed info showing exactly which rows were modified and what each changed value was.

I need to know which tables and views are being read from and how often.

If that is exactly the data you need, then the meta-data will be all that you need. You might even need only a subset. For example, if you want to know only how many times each table was opened/closed by that external process in a given time-frame, journal data is the way to go.

But if you need something more, like which rows were read, then it gets more complicated.

So, can you clarify as much as possible just what you need to accomplish?


Author Comment

by:Lawrence Barnes
ID: 36957943
It looks like I closed this question out while you were responding.  Sorry about that.  Thank you for your conscise repley as the meta-data is exactly what I need.
LVL 27

Expert Comment

ID: 36959813
No problem. I often continue closed questions just to flesh them out. I don't mind points, but I have no real use for them so it doesn't matter.

You can assign multiple files to a single journal. You need to specify that you want to log opens/closes for each file that is journaled. Once opens/closes are journaled, it becomes a matter of invoking the DSPJRN command with the appropriate parameters (or calling the appropriate APIs or whatever method is chosen). You might, for example, have that part encapsulated in a stored proc on the iSeries and it would return a result set back to SQL Server.

If journaling turns out to be infeasible, there is a system exit point associated with database opens/closes. A relatively simple program could be attached to that exit point. When any application opens a database file, the exit program is called. The program could look at two decisions -- Is this from a process that I want to monitor (i.e., is it the external system)? And is this a file that I care about?

Whatever decisions the program uses, it would either write a row into a logging table or it would end. Your SQL Server could access that logging table. Some form of maintenance would be needed to purge old events, etc.

If the access method of the external system is known, it might be better to put an exit program on the exit point for that access method. It would make similar decisions to the general exit program above, though, and write to a similar log table. It wouldn't be quite as active, because it would only react to requests through the network access exit point.

Other possibilities exist. Those are the common ones.


Author Comment

by:Lawrence Barnes
ID: 37003616
Thanks again!

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question