Capturing SQL

Hi, Does anyone know of an easy way to capture SQL that is run against the database. I'm in a Baan/Informix environment and would like to see what sort of SQL is being produced, for curiosity, and also it would allow me to script them to run load tests ... OS: HP-UX 11 DB: Informix 7.30FC6-1 -- All thought welcome, caf com.yahoo@caf0013 -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Extracting all SQL may not be feasible, or even required. A statistically significant sample of SQL may be what you are looking for. Here's a script which creates a sort of SQL profile which I use in any number of situations. In summary, the script identifies 'active' SQL (any session in which the first flag of the onstat -u command is not 'Y'), extracts their onstat -u lines (to provide info. on the locks taken, reads, writes) and extracts their onstat -g sql or ses information. Every time the script runs (I run it every5 minutes thru cron), it time stamps the new entries. Each day has its own 'Profile' file. You can trade statistical significance with 'Profile' file size by controlling the frequency of execution. HTH Rudy P.S. There's another goodie which goes with this. Its a script which parses the 'SQL profile' files, extracting all SQL relating to a specific table (or views on it). #!/bin/ksh # Script to extract 'active' SQL from the Database. if [ $# -lt 1 ] then echo "Usage : $0 <instance> [Detail sql/ses]" exit 1 fi INSTANCE=$1 DETAIL=$2 KEEP_DAYS=60 SQL_TRACE_DIR=/infmscript/$INSTANCE/admin/stats SQL_TRACE=$SQL_TRACE_DIR/getsql.`date "+%Y%m%d"` if [ "$DETAIL" = "" ] then DETAIL=sql fi # Sets up informix variables . /infmscript/admin/script/informix_profile $INSTANCE >> /dev/null date >> $SQL_TRACE if [ $DETAIL = 'sql' ] then onstat -u | grep -v informix | grep -v root | \\ grep -v "^.........Y" | \\ grep "^..........-" | sort +2 >> $SQL_TRACE for SESS in `onstat -u | grep -v informix | grep -v root | \\ grep -v "^.........Y" | \\ grep "^..........-" | nawk -F " " '{ print $3}' | sort -u ` do echo "$SESS \\c" >> $SQL_TRACE onstat -g $DETAIL $SESS | sed -n "/Current SQL/,/^$/p" >> $SQL_TRACE doneelse for SESS in `onstat -u | grep -v informix | grep -v root | \\ grep -v "^.........Y" | \\ grep "^..........-" | nawk -F " " '{ print $3}' | sort -u ` do echo "$SESS \\c" >> $SQL_TRACE onstat -g $DETAIL $SESS >> $SQL_TRACE donefi # Clean up, keeping only the last $KEEP_DAYS traces find $SQL_TRACE_DIR -mtime +$KEEP_DAYS -name "getsql.*" -exec rm -f {} \\; # End of Script In article <7gpie9$g53$1@nnrp1.dejanews.com>, caf <caf0013@my-dejanews.com> wrote: > Hi, > > Does anyone know of an easy way to capture SQL that is run against the > database. I'm in a Baan/Informix environment and would like to see what sort > of SQL is being produced, for curiosity, and also it would allow me to script > them to run load tests ... > > OS: HP-UX 11 > DB: Informix 7.30FC6-1 > > -- > All thought welcome, > > caf > com.yahoo@caf0013 > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Refer to the Zero Impact Sql Monitor and Zero Impact Service Level Monitor at www.sqlpower.com, Scales to thousands of users with no required impact upon the database server, caf wrote in message <7gpie9$g53$1@nnrp1.dejanews.com>... >Hi, > >Does anyone know of an easy way to capture SQL that is run against the >database. I'm in a Baan/Informix environment and would like to see what sort >of SQL is being produced, for curiosity, and also it would allow me to script >them to run load tests ... > >OS: HP-UX 11 >DB: Informix 7.30FC6-1 > >-- >All thought welcome, > >caf >com.yahoo@caf0013 > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

caf <caf0013@my-dejanews.com> wrote in message news:7gpie9$g53$1@nnrp1.dejanews.com... > Hi, > > Does anyone know of an easy way to capture SQL that is run against the > database. I'm in a Baan/Informix environment and would like to see what sort > of SQL is being produced, for curiosity, and also it would allow me to script > them to run load tests ... > This has some overhead but try "SET EXPLAIN ON" after you establish the DB connection.