Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Current SQL from system tables

177 views
Skip to first unread message

fored...@gmail.com

unread,
Oct 8, 2012, 9:07:14 AM10/8/12
to
Good day.

Please advice how to get Current SQL from system tables for particular session (like onstat shows)?

IDS 11.50.FC9W2X3

Bartlomiej Lidke

unread,
Oct 9, 2012, 3:15:51 PM10/9/12
to
fored...@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

fored...@gmail.com

unread,
Oct 12, 2012, 6:12:28 AM10/12/12
to
Thank you for response, but unfortunately the advice didn't help. The most often results are folowed. Output from onstat looks like:

Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
60373 - alr_test DR Not Wait 0 0 9.22 Off
60348 - alr_test CR Wait 15 0 0 3.70 Off
60011 SELECT alr_test CR Not Wait 0 0 9.20 Off
51176 SET OBJMODE nis_etalon CR Not Wait -525 -111 9.20 Off
46282 - nodisca DR Not Wait 0 0 9.20 Off
42479 EXEC PROCEDURE emcs_klp_test CR Wait 120 0 0 9.28 Off
42478 SELECT eds_test CR Wait 120 0 0 9.28 Off
42477 SELECT alr_test DR Wait 120 0 0 9.28 Off
41407 EXEC PROCEDURE emcs_klp CR Wait 120 0 0 9.28 Off
41398 SELECT emcs_klp CR Wait 120 0 0 9.28 Off
36360 EXEC PROCEDURE emcs_klp_test CR Wait 120 0 0 9.28 Off
36359 SELECT eds_test CR Wait 120 0 0 9.28 Off
36358 SELECT alr_test DR Wait 120 0 0 9.28 Off
34547 - nodisca CR Not Wait 0 0 9.30 Off
34531 - nodisca CR Not Wait 0 0 9.30 Off
25068 - - - Not Wait 0 0 9.28 Off
3231 SELECT nodisca CR Not Wait 0 0 3.70 Off
3229 - - - Not Wait 0 0 3.70 Off
1840 SELECT nodisca CR Wait 120 0 0 9.52 Off
1837 SELECT nodisca CR Wait 120 0 0 9.52 Off
154 SELECT sysmaster CR Not Wait 0 0 9.20 Off
78 SELECT eds_ekspl CR Wait 120 0 0 9.28 Off
77 SELECT eds CR Wait 120 0 0 9.28 Off
74 SELECT nodisca CR Wait 120 0 0 9.28 Off
69 SELECT alr_ekspl CR Wait 120 0 0 9.28 Off
66 SELECT alr CR Wait 120 0 0 9.28 Off
63 SELECT nodisca CR Wait 120 0 0 9.28 Off
61 SELECT eds CR Wait 120 0 0 9.28 Off
60 SELECT alr CR Wait 120 0 0 9.28 Off
54 SELECT eds_ekspl CR Wait 120 0 0 9.28 Off
53 SELECT alr_ekspl CR Wait 120 0 0 9.28 Off
51 sysadmin DR Wait 5 0 0 - Off
50 sysadmin DR Wait 5 0 0 - Off
47 sysadmin DR Wait 5 0 0 - Off

But query mentioned returns:

60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
60348|alr_test|COMMITTED READ|15|0|0.00000000000000|0|0|0|0|0|0|-1|0|0|9.03||
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...

Bartlomiej Lidke

unread,
Oct 18, 2012, 5:37:29 PM10/18/12
to
fored...@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

Jason Harris

unread,
Oct 19, 2012, 3:32:50 AM10/19/12
to
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

fored...@gmail.com

unread,
Oct 19, 2012, 6:29:20 AM10/19/12
to
> 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?

Bartlomiej Lidke

unread,
Oct 19, 2012, 5:02:12 PM10/19/12
to
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
0 new messages