SQL Statement Monitor

Hi, John

I am a little rusty on this, so you may want to wait for Tom to reply, but here goes.

My experience with SQL monitors is that you start them against an existing server job. The problem occurs when a new job is started to serve the request; it didn't exist when the monitor was started.

To make sure that you are seeing the correct job:
1) If you know the files that are being updated, you can work with the object lock on that file using;
WRKOBJLCK mylib/mfile *FILE
2)If you know the user that the request logs in under, you can work with the locks on that profile:
WRKOBJLCK user *USRPRF
With these two options, you can tell if the monitor is started against the job serving the request.

Try the above, and I will do some more research, and dust off the appropriate brain cells.
Barry

Thanks for the responses guys.  I tried two approaches based on your advice.  First, I ran STRDBMON JOB(*ALL) TYPE(*DETAIL) OUTFILE(QGPL/DBMON1).  I then used the app in an existing green screen for a few minutes doing things I know access the DB.  I then issued a ENDDBMON JOB(*ALL) and used the Client Access "Transfer Data From iSeries" to take a look at QGPL/DBMON1.  It only had 2 records in it which were not relevant to what I had done.  My next approach was to start another monitor (this time to QGPL/DBMON2) and started a new instance of the app in a new green screen.  After doing some work for a few minutes and pulling up different data-driven screens, I again issued ENDDBMON JOB(*ALL) and used the same method to retrieve QGPL/DBMON2.  Again, the same two records and no relevant data.  Any ideas?  The DB Monitor didn't seem to collect all the incoming SQL statements to the DB.  I am accessing screens I know hit certain tables...yet no SQL statments are showing up in the monitor results.
Thanks,
John

Dave,
The app is hosted on the 400 and only accessed through a Client Access.
Thanks,
John

Hi
in that case it could be using OPNQRYF, this is the oriiginal way the AS/400 use to query a DB before SQL

I don't thinks that this captured by the db monitor as it is not translated to SQL - Tom / Barry may know more about that.

Dave

John:

Dave probably is right. If the actual app runs on the AS/400, and STRDBMON JOB(*ALL) logs no SQL statements, then it isn't using SQL. OPNQRYF is a good possibility but might not be necessary -- the app might be doing direct native I/O against the files. As I alluded to, there might not be any SQL to report.

Even if the app is on the PC, it could be doing basic DDM access, e.g., via the Visual RPG DDM .dlls or even the Java toolkit DDM record-level access classes (which might not involve SQL though I'm not sure).

At least one other possibility is that the DB monitor needs PTFs.

For _any_ significant database work, the latest DB2 group PTF should always be applied. I think the DB2 group for V5R2 is SF99502. Please post the level of that PTF; you can find the level with the WRKPTFGRP command.

Tom

wkrptfgrp does not give any results.  It simply displays "(No PTF groups found.)"  If the program is using OPNQRYF is it possible to monitor the queries it's issuing?  If it's doing direct file I/O (which seems highly unlikely) I'm guessing there's no way to effectively monitor that as well.  I suppose it may be possible to monitor open file "handles" or something.  Monitoring OPNQRYF seems like the most promising.  What is surprising is that this app was recently re-written only a couple of years ago and OPNQRYF seems to be a very outdated way to access data.

Thanks guys.

John:

Wow. If _none_ show up, then I'd be worried about how long since even a cume PTF package has been applied, much less the latest DB2 group. I'd recommend ordering a current cume PTF package and installing it ASAP. It should come with the DB2 group as well as the HIPER package. All three _need_ to be applied. This is becoming particularly important as V5R2 is reaching end-of-support.

Don't dismiss OPNQRYF too easily. There are a number of things that it can do that aren't reasonably possible any other way. For example, it can create an access path over joined files where a compound key consists of fields in the different files (i.e., CUSTOMER# in the primary file CUSTOMER# and ORDER# in the secondary file, and ORDER# and LINE# in the tertiary file -- a single I/O operation then can directly access a specific line in a specific order for a customer). The ability to do direct record I/O over such a file can be an incredible boost for response time. Other abilities also exist.

SQL excels in set-at-a-time. But direct record access? It can be a hog, especially if indexes must be temporary.

OPNQRYF is especially handy as a way to link info from separate apps. Each app might have its own well-defined database, but two apps commonly know little about another's database. OPNQRYF can be the glue that creates the relationship.

Commercial relational databases got their big start on AS/400s (well, the System/38 previously). OPNQRYF has had a long time to evolve into a useful tool.

Tom

John:

If no SQL is involved, OPNQRYF _might_ be used if various forms of dynamic selection is needed. But I wouldn't usually expect to see it except for specific kinds of functions.

Most likely, OPNQRYF simply isn't needed. The app has the physical and logical files (i.e., the tables, views and indexes) that are needed already. The app's programs have all that's needed to do all of the sequential and random access to fulfill the app's function.

If we're talking native I/O, then you might be looking more at either journaling or triggers to do the logging. I'd start with a journal and add needed refinements as they become apparent. (Of course, these also apply to SQL tables.)

Tom