Current SQL from system tables

Good day. Please advice how to get Current SQL from system tables for particular session (like onstat shows)? IDS 11.50.FC9W2X3

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

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...

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

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