Empty `onstat -g his`

Hi, That's my onconfig setting: SQLTRACE level=med,ntraces=1000,size=2,mode=user And that is what `onstat -g his` shows: IBM Informix Dynamic Server Version 11.70.FC5XE -- On-Line -- Up 22:33:44 -- 344686176 KbytesStatement history: No statement trace data is available. A syssqltrace table is empty too. Why? ------------------------------ Sincerely, Dennis ------------------------------

Try setting the mode to global , unless you are trying to run the trace for individual users. If you are trying to capture the trace information for individual users, then look at the API task "set sql user tracing on" to enable it for a session. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi Dennis. Looks like SQLTRACE isn't running at all. The configuration parameter needs to be there at startup. Alternatively, use this SQL so you don't have to restart the instance: https://www.ibm.com/docs/en/informix-servers/14.10?topic=saaf-set-sql-tracing-argument-set-global-sql-tracing-sql-administration-api Easier still, use InformixHQ or AGS Server Studio. ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Hi Doug, Please could you be a little bit more detailed on Server Studio? ------------------------------ Sincerely, Dennis ------------------------------

Hi Denis. That's covered from around 2 minutes into this video: https://www.serverstudio.co.uk/video-demos/#video-13 ------------------------------ Doug Lawry Oninit Consulting ------------------------------

Dennis: Here is a script that will help you manage SQLTRACE. You should still read about it in the Informix manuals to understand the capabilities and how it works. ######################################## # SetSQLTrace ###################################### #!/usr/bin/ksh info() { echo "" dbaccess sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}' select "Databases traced: " || task("set sql tracing database list") from systables where tabid = 1; select "Users traced: " || task("set sql tracing user list") from systables where tabid = 1; execute function task ("set sql tracing info");EOF } usage () { echo "Usage: $(basename $0) [-u user] [-d database] [-s size_kb] [-n ntraces] [-<h|m|l>]"; echo " [-aAcCoOSR]"; echo ""; echo " -u - Specify a user to trace"; echo " -d - Specify a database to trace"; echo " -I - Specify a single session id to trace"; echo " -s - Specify the size of each trace in KB. [Default: 4]"; echo " -n - Specify the number of queries to keep in memory"; echo " -h - Specify trace level HIGH "; echo " -m - Specify trace level MEDIUM "; echo " -l - Specify trace level LOW "; echo " -a - Append users to existing trace list"; echo " -A - Append databases to existing trace list"; echo " -c - Clear user list. Append to empty list when used with -a"; echo " -C - Clear database list. Append to empty list when used with -A"; echo " -o - Turn tracing on with defaults"; echo " -O - Turn tracing off"; echo " -S - Suspend tracing"; echo " -R - Resume tracing"; echo " -f - Save SQLTrace settings"; echo " -i - Report SQLTrace state"; echo ""; echo " -u & -d may be specified multiple times to trace multiple"; echo " users and/or databases."; echo ""; } if [[ $# -eq 0 ]]; then usage exit 1; fi ndbs=0 nusers=0 nsids=0 level=medium levelset=0 size=4 sizeset=0 num=2000 numset=0 save=0 mode=global modeset=0 append_dbs=0 append_user=0 dbclear=0 usrclr=0 params=0 unset user unset dbase unset sid while getopts "u:d:s:n:I:himlAaCcfOoSR" chr $* do case $chr in O) dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task ("set sql tracing off");EOF exit $? ;; i) info exit 0 ;; o) dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task ("set sql tracing on", 2000, 4);select "Databases traced: " || task("set sql tracing database list") from systables where tabid = 1; select "Users traced: " || task("set sql tracing user list") from systables where tabid = 1; EOF exit $? ;; S) dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task ("set sql tracing suspend"); execute function task ("set sql tracing info");select "Databases traced: " || task("set sql tracing database list") from systables where tabid = 1; select "Users traced: " || task("set sql tracing user list") from systables where tabid = 1; EOF exit $? ;; R) dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task ("set sql tracing resume"); execute function task ("set sql tracing info");select "Databases traced: " || task("set sql tracing database list") from systables where tabid = 1; select "Users traced: " || task("set sql tracing user list") from systables where tabid = 1; EOF exit $? ;; C) dbclear=1; SHFTS=$(( SHFTS + 1 )) ;; c) usrclr=1; SHFTS=$(( SHFTS + 1 )) ;; A) append_dbs=1; dbclear=0; SHFTS=$(( SHFTS + 1 )) ;; a) append_user=1; usrclr=0; SHFTS=$(( SHFTS + 1 )) ;; f) save=1; SHFTS=$(( SHFTS + 1 )) ;; u) user[nusers]=$OPTARG; nusers=$(( nusers + 1 )) SHFTS=$(( SHFTS + 2 )) ;; d) dbase[ndbs]=$OPTARG ndbs=$(( ndbs + 1 )) SHFTS=$(( SHFTS + 2 )) ;; I) sid[$nsids]=$OPTARG nsids=$(( sids + 1 )) SHFTS=$(( SHFTS + 2 )) ;; s) size=$OPTARG sizeset=1 params=1; SHFTS=$(( SHFTS + 2 )) ;; n) num=$OPTARG params=1; numset=1; SHFTS=$(( SHFTS + 2 )) ;; h) level=high; params=1; SHFTS=$(( SHFTS + 1 )) ;; m) level=medium; params=1; SHFTS=$(( SHFTS + 1 )) ;; l) level=low; params=1; SHFTS=$(( SHFTS + 1 )) ;; ?) usage exit 1; ;; esac done shift $(( SHFTS )) if [[ $append_user -eq 1 && $nusers -eq 0 ]]; then echo "-a requires at least one user listed (-u)" usage exit 1 fi if [[ $append_dbs -eq 1 && $ndbs -eq 0 ]]; then echo "-A requires at least one database listed (-d)" usage exit 1 fi if [[ ($append_dbs -eq 1|| $append_user -eq 1) && $params ]]; then echo "Trace parameters ignored when appending users and/or databases." fi if [[ ${#user[*]} -gt 0 ]]; then mode=user; fi dbaccess sysmaster - <<EOF 2>/dev/null unload to /tmp/sqltracedtl select * from syssqltrace_info;EOF if [[ ! -e /tmp/sqltracedtl ]]; then exit 1 fi if [[ $numset -eq 0 ]]; then num=$( awk -F\\| '{ print $2; }' /tmp/sqltracedtl ); fi if [[ sizeset -eq 0 ]]; then size=$( awk -F\\| '{ printf "%d\\n", ($3+1023)/1024; }' /tmp/sqltracedtl ); fi if [[ $dbclear -eq 1 ]]; then dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'| grep -v '^Database 'execute function task("set sql tracing database clear");EOF fi if [[ $usrclr -eq 1 ]]; then dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task("set sql tracing user clear");EOF fi if [[ $nusers -gt 0 ]]; then for user in ${user[*]}; do echo "execute function task(\\"set sql tracing user add\\", \\"$user\\" );" done | dbaccess -e sysadmin - 2>>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}' fi if [[ $ndbs -gt 0 ]]; then for dbase in ${dbase[*]}; do echo "execute function task(\\"set sql tracing database add\\", \\"$dbase\\" );" done | dbaccess -e sysadmin - 2>>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}' fi if [[ $nsids -gt 0 ]]; then for sesid in ${sid[*]}; do echo "execute function task(\\"set sql tracing session\\", "on", $sesid );" done | dbaccess -e sysadmin - 2>>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}' fi if [[ $append_user -eq 0 && $append_dbs -eq 0 ]]; then # Leave tracing parameters the same if appending users or databases. echo "Setting global mode with $num traces of $size KB, level $level mode: $mode." dbaccess -e sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}'execute function task ("set sql tracing on", $num, "${size}", "$level", "$mode");EOF fi if [[ $save ]]; then echo "Saving SQLTrace mode settings"; dbaccess sysadmin <<EOF 2>/dev/null | awk '{ sub( "\\\\(expression\\\\) ", "" ); print $0; next;}' update ph_task set tk_enable = "t" where tk_name = "Save SQL Trace"; EOF fi info if [[ -e /tmp/sqltracedtl ]]; then rm /tmp/sqltracedtl; fi exit 0 ################################################# And here is the Usage output: $ SetSQLTrace Usage: SetSQLTrace [-u user] [-d database] [-s size_kb] [-n ntraces] [-<h|m|l>] [-aAcCoOSR] -u - Specify a user to trace -d - Specify a database to trace -I - Specify a single session id to trace -s - Specify the size of each trace in KB. [Default: 4] -n - Specify the number of queries to keep in memory -h - Specify trace level HIGH -m - Specify trace level MEDIUM -l - Specify trace level LOW -a - Append users to existing trace list -A - Append databases to existing trace list -c - Clear user list. Append to empty list when used with -a -C - Clear database list. Append to empty list when used with -A -o - Turn tracing on with defaults -O - Turn tracing off -S - Suspend tracing -R - Resume tracing -f - Save SQLTrace settings -i - Report SQLTrace state -u & -d may be specified multiple times to trace multiple users and/or databases. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Doug: FYI, Server Studio's SQL Trace does not have an option to resume tracing after suspending. I.e., changing Trace State to On from Suspended does not turn it on actually. ------------------------------ Sincerely, Dennis ------------------------------

Hi Denis. I will raise that with AGS. Meanwhile: EXECUTE FUNCTION sysadmin:task("set sql tracing resume");https://www.ibm.com/docs/en/informix-servers/14.10?topic=saaf-set-sql-tracing-argument-set-global-sql-tracing-sql-administration-api ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Doug: FYI, Server Studio's SQL Trace does not have an option to resume tracing after suspending. I.e., changing Trace State to On from Suspended does not turn it on actually. ------------------------------ Sincerely, Dennis ------------------------------

AGS have fixed that in version 10.5 Build 21124 which you can get from www.serverstudio.com/downloads or use NetUpdate. ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Hi Denis. I will raise that with AGS. Meanwhile: EXECUTE FUNCTION sysadmin:task("set sql tracing resume");https://www.ibm.com/docs/en/informix-servers/14.10?topic=saaf-set-sql-tracing-argument-set-global-sql-tracing-sql-administration-api ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Original Message: Sent: Tue June 18, 2024 07:06 AM From: Dennis Melnikov Subject: Empty `onstat -g his` Doug: FYI, Server Studio's SQL Trace does not have an option to resume tracing after suspending. I.e., changing Trace State to On from Suspended does not turn it on actually. ------------------------------ Sincerely, Dennis ------------------------------

Hi, Please, how to trace a single session? Seems like `EXECUTE FUNCTION task("set sql tracing session", "on", $sid)` is not enough. ------------------------------ Sincerely, Dennis ------------------------------

Hi Denis. You can't specify one session ID, but only databases and users. ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Hi, Please, how to trace a single session? Seems like `EXECUTE FUNCTION task("set sql tracing session", "on", $sid)` is not enough. ------------------------------ Sincerely, Dennis ------------------------------

Doug: Hmm, what is "set sql tracing session" for? As the guide reads, Use the on argument to turn on tracing for the session, even if the global tracing policy is set to disable tracing. ------------------------------ Sincerely, Dennis ------------------------------

That means for the Informix instance until it is shutdown. Badly worded, I agree! ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Doug: Hmm, what is "set sql tracing session" for? As the guide reads, Use the on argument to turn on tracing for the session, even if the global tracing policy is set to disable tracing. ------------------------------ Sincerely, Dennis ------------------------------ Original Message: Sent: Wed July 03, 2024 10:14 AM From: Doug Lawry Subject: Empty `onstat -g his` Hi Denis. You can't specify one session ID, but only databases and users. ------------------------------ Doug Lawry Oninit Consulting Original Message: Sent: Wed July 03, 2024 09:37 AM From: Dennis Melnikov Subject: Empty `onstat -g his` Hi, Please, how to trace a single session? Seems like `EXECUTE FUNCTION task("set sql tracing session", "on", $sid)` is not enough. ------------------------------ Sincerely, Dennis

Doug: I still don't understand what does it mean. How is it supposed to work? ------------------------------ Sincerely, Dennis ------------------------------

Not true Doug. You can set SQL TRACE on for: globally a database or databases a user or users a session Not sure why it's not working for Dennis. Dennis: Is it possible that all of the queries issued by that session were prepared before you could initiate the tracing? That's the only thing I can think of. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

You're right Art. Never used that, have now found in the IDS documentation. Not an option in the Server Studio or InformixHQ GUIs. ------------------------------ Doug Lawry Oninit Consulting ------------------------------ Not true Doug. You can set SQL TRACE on for: globally a database or databases a user or users a session Not sure why it's not working for Dennis. Dennis: Is it possible that all of the queries issued by that session were prepared before you could initiate the tracing? That's the only thing I can think of. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------ Original Message: Sent: Wed July 03, 2024 10:14 AM From: Doug Lawry Subject: Empty `onstat -g his` Hi Denis. You can't specify one session ID, but only databases and users. ------------------------------ Doug Lawry Oninit Consulting Original Message: Sent: Wed July 03, 2024 09:37 AM From: Dennis Melnikov Subject: Empty `onstat -g his` Hi, Please, how to trace a single session? Seems like `EXECUTE FUNCTION task("set sql tracing session", "on", $sid)` is not enough. ------------------------------ Sincerely, Dennis

Art: Can that be a matter of 11.70? Or may be I am missing something? What is a working sequence to turn on tracing on a single session? ------------------------------ Sincerely, Dennis ------------------------------

Dennis: Ahh, that's your problem! V11.70 only supported global and user modes. No support yet for database or session. They came later. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------