real-time monitoring of SQL commands?

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