Translating with DrWatson… this can take a few seconds the first time.
This is a genuine, complex translation. DrWatson protects commands, error codes, and log output while naturally translating the surrounding text. It’s translated once and saved.
The poster asked how to retrieve the current SQL text per session from sysmaster tables, as onstat shows, on IDS 11.50.FC9W2X3. Suggestions were to use the syssqlcurses definition in sysmaster.sql (commenting out the sdb_iscurrent/odb_iscurrent checks) or query sysconblock.cbl_stmt directly, but results were erratic: only one session returned, empty statement text, and filtering by session id often returned nothing, since sysconblock is an internal virtual table that mainly catches longer-running queries. syssqlstat was offered as an alternative, though its statement column is limited to 200 chars and is buggy on 11.50 (IC86488), working properly only on 11.70; sqltrace shows past, not current, statements. No satisfactory resolution for 11.50 is recorded.
foredstest@gmail.com wrote:
> Please advice how to get Current SQL from system tables for particular
> session (like onstat shows)?
if you don't want to enable sqltrace then please look at the syssqlcurses
inside sysmaster.sql file. comment sdb_iscurrent/odb_iscurrent conditions
to see statements for other sessions (taken from sysconblock.cbl_stmt)
be warned that this is not supported way
--
butthead
foredstest@gmail.com wrote:
> But query mentioned returns:
>
> 60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
[...]
> So, there is information about one session only and all rows contain empty cbl_stmt column...
have you tried to select directly from sysconblock?
--
butthead
↪ replying to foredstest@gmail.com
Jason Harris — — source: Usenet: comp.databases.informix
Hi,
Most of that info is in syssqlstat.
Why do you need it from sysmaster if it is already there in onstat output?
Good luck,
Jason
On Monday, 8 October 2012 21:07:15 UTC+8, fored...@gmail.com wrote:
> Good day.
>
>
>
> Please advice how to get Current SQL from system tables for particular session (like onstat shows)?
>
>
>
> IDS 11.50.FC9W2X3
> 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?
foredstest@gmail.com wrote:
>> 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
--
butthead
We use strictly necessary cookies to make this site work. With your
consent we’d also use optional cookies for analytics and marketing. You can accept all,
reject all, or choose. Read our Cookie Policy.