Query tracer

Hi, I am looking for a way to trace realy each SQL statement an application sends to the database server. Because the source code of the application cannot be changed I have to use means of the DBMS or a tracing tool. I tried: onstat -g sql sid -r seconds > file. The minimum interval is 1 second so if there are sql statements in between they will not be recorded. I also tried SQLIDEBUG variable and sqliprint, but I can hardly interpret the output. I-Spy is the thing I'm looking for, but I-Spy is neither available for Linux nor for Reliant Unix. Can anybody give me a hint if there is a more useful tool? TIA Reinhard

Reinhard Habichtsberg wrote: > > Hi, > > I am looking for a way to trace realy each SQL statement > an application sends to the database server. > > Because the source code of the application cannot be > changed I have to use means of the DBMS or a tracing tool. > > I tried: onstat -g sql sid -r seconds > file. The minimum > interval is 1 second so if there are sql statements in between > they will not be recorded. > > I also tried SQLIDEBUG variable and sqliprint, but I can hardly > interpret the output. > Have you used SQLIPRINT to interpret the SQLIDEBUG output? -- John Carlson Informix DBA WHSmith USA #include std_disclaimer.h /* These are my opinions, not my company's opinion */

Yes I did. "Carlson@WHSmith" schrieb: > > Reinhard Habichtsberg wrote: > > > > Hi, > > > > I am looking for a way to trace realy each SQL statement > > an application sends to the database server. > > > > Because the source code of the application cannot be > > changed I have to use means of the DBMS or a tracing tool. > > > > I tried: onstat -g sql sid -r seconds > file. The minimum > > interval is 1 second so if there are sql statements in between > > they will not be recorded. > > > > I also tried SQLIDEBUG variable and sqliprint, but I can hardly > > interpret the output. > > > > Have you used SQLIPRINT to interpret the SQLIDEBUG output? > > -- > John Carlson > Informix DBA > WHSmith USA > > #include std_disclaimer.h /* These are my opinions, not my company's > opinion */

Reinhard Habichtsberg wrote: > Hi, > > I am looking for a way to trace realy each SQL statement > an application sends to the database server. > > Because the source code of the application cannot be > changed I have to use means of the DBMS or a tracing tool. > > I tried: onstat -g sql sid -r seconds > file. The minimum > interval is 1 second so if there are sql statements in between > they will not be recorded. > > I also tried SQLIDEBUG variable and sqliprint, but I can hardly > interpret the output. > > I-Spy is the thing I'm looking for, but I-Spy is neither available > for Linux nor for Reliant Unix. > > Can anybody give me a hint if there is a more useful tool? > > TIA > Reinhard I use a program (shell script) that essentially uses onstat -g sql, but tries to be "smart" in the following ways - slows down "onstat -g sql" polling if sql is repeated - speeds up polling if sql changes, going ultimately to a "no delay" state - does not print repeated SQL Since it uses "onstat -g sql" as its driver, it suffers from the fundamental problem that some SQL statements may slip thru even when there is no time delay between repeated "onstat -g sql" statements. Try it out, if you like. Feedback, tweak suggestions, etc are always welcome. Rudy #!/bin/ksh # rudy Wed Mar 3 15:37:51 EST 1999 # Retrieves SQL profile for a session. if [ $# -lt 1 ] then echo "Usage : $0 <session_id> [interval in seconds <default-1>] " exit 1 fi SESS=$1 INTERVAL=$2 if [ "$INTERVAL" = "" ] then INTERVAL=1 fi TMP_FILE1=$0.$$.1 TMP_FILE2=$0.$$.2 ABNORMAL="Abnormal termination. Cleaning up and exiting..." trap "echo $ABNORMAL;rm -f $TMP_FILE1 $TMP_FILE2 ; exit 1" 1 2 13 15 > $TMP_FILE1 > $TMP_FILE2 INACTIVE_RETRIES=0 REPEAT_RETRIES=0 REPEAT_DIFF=0 ONSTATU_CHECK='ON' SLOWDOWN_AFTER=3 # If sql repeats more than this, start slowing down polling MAX_SLEEP=20 # Polling interval should not exceed this during slowdown SWITCH_TO_NO_SLEEP=2 # If SQL changing rapidly, don't sleep at all onstat -g sql $SESS | sed -n "/Current SQL/,/^$/p" > $TMP_FILE1 while [ -s $0 ]do if [ $REPEAT_RETRIES -lt $SLOWDOWN_AFTER -o $ONSTATU_CHECK = 'ON' ]; then onstat -u | grep " $SESS " if [ $? != 0 ] then echo Exiting after onstat u returned nothing break fi ONSTATU_CHECK='OFF' else ONSTATU_CHECK='ON' SLEEP_TIME=`expr $REPEAT_RETRIES \\* $INTERVAL / $SLOWDOWN_AFTER` if [ $SLEEP_TIME -gt $MAX_SLEEP ]; then sleep $MAX_SLEEP else sleep $SLEEP_TIME fi sleep $INTERVAL continue fi onstat -g sql $SESS | sed -n "/Current SQL/,/^$/p" > $TMP_FILE1 if [ -s $TMP_FILE1 ] then cmp -s $TMP_FILE1 $TMP_FILE2 if [ $? != 0 ] # Not identical files then date cat $TMP_FILE1 REPEAT_RETRIES=0 REPEAT_DIFF=`expr $REPEAT_DIFF + 1` else REPEAT_DIFF=0 REPEAT_RETRIES=`expr $REPEAT_RETRIES + 1` echo `date` SQL repeated fi INACTIVE_RETRIES=0 else REPEAT_DIFF=0 echo `date` "No Active SQL found " INACTIVE_RETRIES=`expr $INACTIVE_RETRIES + 1` if [ $INACTIVE_RETRIES -gt 9 ]; then echo Exiting after 10 tries returned no Active SQL break fi fi # Don't sleep at all if SQL is rapidly changing if [ $REPEAT_DIFF -lt $SWITCH_TO_NO_SLEEP ]; then sleep $INTERVAL fi mv $TMP_FILE1 $TMP_FILE2 done rm -f $TMP_FILE1 $TMP_FILE2 ### End ###