real-time monitoring of SQL commands?
Matthew Cornell (IDS 9.20.UC1, RH6.2) wanted to see full SQL statements issued by a Java program, as onstat -g sql -r 1 shows only the type. Ray Canuel suggested onstat -g sql with a session id and SET EXPLAIN ON. smooth1 recommended I-SPY. Rudy Fernandes posted a loop script running onstat -g sql repeatedly and suggested periodic sampling.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi Folks,
I'm running IDS.2000 9.20.UC1 on RH6.2. While my Java program is running
I'd
like a utility that lets me monitor the full/exact SQL commands that it
issues. Is there such a beast? I know of onstat -g sql -r 1, but it only
lists the *type* of command, not the whole thing. Plus, it can miss
commands
that come quickly. onperf didn't seem to have much. I haven't tried the
new
tool that requires a web server (jeez!) Thanks in advance.
matt
Try onstat -g sql {session id} -r 1
That actually lists the SQL statement itself. To get better than 1 second
granularity, you need to run set explain on from within your program or a
stored procedure. This puts every SQl command out to a sqexplain.out file.
Regards,
Ray
"Matthew Cornell" <cornell@cs.umass.edu> wrote in message
news:3A087213.5C8E1DDD@cs.umass.edu...
> Hi Folks,
>
> I'm running IDS.2000 9.20.UC1 on RH6.2. While my Java program is running
> I'd
> like a utility that lets me monitor the full/exact SQL commands that it
> issues. Is there such a beast? I know of onstat -g sql -r 1, but it only
> lists the *type* of command, not the whole thing. Plus, it can miss
> commands
> that come quickly. onperf didn't seem to have much. I haven't tried the
> new
> tool that requires a web server (jeez!) Thanks in advance.
>
> matt
You need I-SPY
Go to www.informix.com and look under
Products
Tools
Matthew Cornell wrote in message <3A087213.5C8E1DDD@cs.umass.edu>...
>Hi Folks,
>
>I'm running IDS.2000 9.20.UC1 on RH6.2. While my Java program is running
>I'd
>like a utility that lets me monitor the full/exact SQL commands that it
>issues. Is there such a beast? I know of onstat -g sql -r 1, but it only
>lists the *type* of command, not the whole thing. Plus, it can miss
>commands
>that come quickly. onperf didn't seem to have much. I haven't tried the
>new
>tool that requires a web server (jeez!) Thanks in advance.
>
>matt
After determining the SessionId of the Java Program run "onstat -g sql
<session_id>". Tacking "-r 1" to the command repeats it every second (as
you know). More detailed granularity can be obtained by placing it a
script that just keeps churning out the command without waiting.
e.g
#!/bin/ksh
SID=$1
integer REPETITIONS=$2
LOGFILE=$SID.log
integer i=0
while [ $i != $REPETITIONS ]; do
onstat -g sql $1 >> $LOGFILE
echo $i
i=`expr $i + 1`done
You could also buy the Informix Handbook available at
http://www.informixhandbook.com/ (Ron is going to love this!) and refer to
the chapter on Application Programming for a smarter (arguably, I'm sure)
script to extract SQL of a session.
Finally, you may want to consider sampling, taking snapshots of your Java
application's sql periodically and developing a "profile" over time - it
tends to be remarkably accurate for "live" use of an application.
Rudy
Matthew Cornell wrote:
> Hi Folks,
>
> I'm running IDS.2000 9.20.UC1 on RH6.2. While my Java program is running
> I'd
> like a utility that lets me monitor the full/exact SQL commands that it
> issues. Is there such a beast? I know of onstat -g sql -r 1, but it only
> lists the *type* of command, not the whole thing. Plus, it can miss
> commands
> that come quickly. onperf didn't seem to have much. I haven't tried the
> new
> tool that requires a web server (jeez!) Thanks in advance.
>
> matt
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g