>> have you tried to select directly from sysconblock?
> Yes, I have tried, but without success. I can't understand such strange behaviour of this table.
> For instance, query without any conditions returns many rows with particular session id, but query
> with this particular session id in where clause doesn't return any rows at all. Can You be a little
> bit more detailed in Your advice?
as sysconblock is internal virtual table you may expect anything and nothing.
when I selected the other session in 'where' I was able to catch it when it
was running more complicated query:
(select count(*) from systables a, systables b, systables c) but I could not
catch it when it was running simple query (select count(*) from foo; -- foo
contains no data)
this was on 11.50uc9w1 and 11.70uc4
you can also try syssqlstat which works fine on 11.70 but is not usable on 11.50 because of
IC86488: SYSMASTER:SYSSQLSTAT SHOWS WRONG STATEMENT IN SQS_STATEMENT COLUMN
unfortunately the sqs_statement field length is only 200 but this table will give you last
statement (if the session is idle now in terms of sql activity) or currently running one
sqltrace will not give you the current but history/last statement