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

Posted on 2011-10-12
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


    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

    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
    LVL 5

    Author Closing Comment

    by:Lawrence Barnes
    Thank you
    LVL 26

    Expert Comment

    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?

    LVL 5

    Author Comment

    by:Lawrence Barnes
    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 26

    Expert Comment

    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.

    LVL 5

    Author Comment

    by:Lawrence Barnes
    Thanks again!

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Suggested Solutions

    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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now