Solved

SQL Statement Monitor

Posted on 2006-07-13
12
437 Views
Last Modified: 2010-08-05
We're trying to record all SQL statements issued against our DB2/400.  We're running V5R2.  Played around with SQL Performance Monitors quite a bit in the Operations Navigator, but we are running an application on the 400 via Client Access that we know is executing queries against the database--yet these queries do NOT show up in any performance monitor.  I thought perhaps that because SQL Performance Monitor does not record SQL queries from "pre-existing" jobs (correct?) that logging into a new green screen and starting a new instance of the app would solve our problem.  So, we logged and noted the job that newly started app was running under and set a monitor on that job.   However, no data showed up despite the fact we know the app ran severeal queries.  We can't see any of the SQL the app is executing.  Even tried monitoring all jobs with no luck.  Since we know what tables the app is hitting we know what to look for.  Is there another tool which should be used instead of this one?

If we run SQL against the DB using "Run an SQL script" these statements DO show up in .  Any ideas?

Thanks,
John
0
Comment
Question by:lakers2003
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 13

Expert Comment

by:_b_h
Comment Utility
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

0
 
LVL 27

Accepted Solution

by:
tliotta earned 250 total points
Comment Utility
John:

Not knowing anything about the client app, it's hard to be precise. I suppose the best place to start is with STRDBMON JOB(*ALL) TYPE(*DETAIL). Not any performance monitor, but the database monitor itself. Specify additional parms on the command as needed, such as the name & location of a file that will log the accesses.

Start the DB monitor, then run the client app for a short time, end the monitor with ENDDBMON JOB(*ALL), and then start querying the log file. Keep in mind that _all_ SQL access will be logged during this period; that's why quick testing is suggested. Far less to wade through and fewer potential distractions.

That should tell you (1) whether SQL is even involved in this and (2) what characteristics you might want to monitor on an ongoing basis.

AFAIK, if the database connection is through DRDA, then the database monitor is your _only_ choice. Most ODBC connections don't run as DRDA; but Client Access generally does. Not surprising since IBM is involved on both ends of that one.

If you collect enough info to be confused, don't hesitate to post back. DB monitoring isn't trivial.

Tom
0
 

Author Comment

by:lakers2003
Comment Utility
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
0
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 250 total points
Comment Utility
Hi
just one thouht, do you have access to the source for the PC application.
I have known PC pplications that extract data via third party programs (ie generated by Lansa) or storred procedures.

These would not show up on the monitor.

Dave
0
 

Author Comment

by:lakers2003
Comment Utility
Dave,
The app is hosted on the 400 and only accessed through a Client Access.
Thanks,
John
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 14

Expert Comment

by:daveslater
Comment Utility
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
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0
 

Author Comment

by:lakers2003
Comment Utility
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.
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0
 
LVL 27

Expert Comment

by:tliotta
Comment Utility
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
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Following basic email etiquette rules will help you write a professional email and achieve a good, lasting impression with your contacts.
HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

12 Experts available now in Live!

Get 1:1 Help Now