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------------------------------
Original Message:
Sent: Tue June 11, 2024 09:07 AM
From: Dennis Melnikov
Subject: Empty `onstat -g his`
Hi Doug,
Please could you be a little bit more detailed on Server Studio?
------------------------------
Sincerely,
Dennis
Original Message:
Sent: Wed November 15, 2023 02:55 AM
From: Doug Lawry
Subject: Empty `onstat -g his`
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
Original Message:
Sent: Tue November 14, 2023 08:46 AM
From: Dennis Melnikov
Subject: 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 Kbytes
Statement history:
No statement trace data is available.
A syssqltrace table is empty too. Why?
------------------------------
Sincerely,
Dennis
------------------------------