Hi, folks,

following sql returns all running sessions for dateabase "pegasusdb" and host "BENONI3" :

CONNECT TO 'sysmaster';

SELECT t0.sqs_sessionid, t0.sqs_statement FROM syssqlstat AS t0, syssessions AS t1
WHERE t0.sqs_dbname = 'pegasusdb'
AND NOT t0.sqs_statement IS NULL
AND t0.sqs_sessionid = t1.sid AND t1.tty = 'BENONI3'
ORDER BY t0.sqs_sessionid DESC

Nevertheless, the sql-statement will be truncated after 200 characters because of column width. Any ideas what can be used instead without truncating ?

I'm wondering how onstat -g sql <sid> will do that ...

Yours, Christof.

Hi Christof,

With onstat you can do the following:
onstat -g ses | grep BENONI3

This will return all session ids from this host.
Then you can write sample shell script like this one:

$ vi onstat_g.sh

# $1 hostname
# $2 sleep time

while true
ses="$1"
do
for i in `onstat -g ses | grep $1 | awk '{print $1}'`
do
# the next line creates file onstat_g_ses containing the sql statement for current session
onstat -g ses $i >> onstat_g_ses
ses=$ses", "$i
done
# Just view some output - hostname, session id and current date
echo $ses" - "`date`
sleep $2
done
# END

So you can execute this script (parameters are hostname and sleep time in seconds to wait before repeat the script):

$ onstat_g.ksh BENONI3 60

Hope this will help you.

PS.
You have to change mode to execute the script.
You have to interrupt the script manually (Ctr+C)

Hi kmstanev,

thanks for your reply and support.

Using your shell script could work, but nevertheless for technical reasons, I need a solution in SQL.

Many greetings, Christof.

Hi
look at table sysconblock(cbl_stmt) or view syssqexplain(sqx_sqlstatement).

Sometimes query (select *) from these tables return no or too few recors.