querying sysmaster to get last sql executed

Hi all, Does anyone know how to get current or last sql sentence execute by a session? I know that onstat -g ses <session id> shows it, but I want to get it trough sysmaster. is it possible? Regards

SELECT scs_sqlstatement FROM syssqlcurses WHERE scs_sessionid = <sessid>; Art S. Kagel ----- Original Message ----- From: Paulo Rafael Padilla Velazquez <ids@iiug.org> At: 3/14 12:08:54 Hi all, Does anyone know how to get current or last sql sentence execute by a session? I know that onstat -g ses <session id> shows it, but I want to get it trough sysmaster. is it possible? Regards ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Art, Does Exist any document or reference where describe sysmaster tables? I have The informix Handbook, but it explains sysmaster ids v 7.x tables. Regards

http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/co m.ibm.docnotes.doc/uc4/ids_adref_docnotes_10.0.html > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of PAULO RAFAEL PADILLA VELAZQUEZ > Sent: Wednesday, March 14, 2007 1:14 PM > To: ids@iiug.org > Subject: Re: querying sysmaster to get last sql executed [8667] > > Thanks Art, > > Does Exist any document or reference where describe sysmaster tables? > > I have The informix Handbook, but it explains sysmaster ids v > 7.x tables. > > Regards > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > >

Lester Knutsen's web site, www.advancedatatools.com, has several articles, presentations, and scripts related to the sysmaster/SMI database. Lester's the leading sysmaster guru. I also refer to the sysmaster.sql script in $INFORMIXDIR/lib in which there are lots of comments that help one understand those pseudotables. Art S. Kagel ----- Original Message ----- From: Paulo Rafael Padilla Velazquez <ids@iiug.org> At: 3/14 15:14:37 Thanks Art, Does Exist any document or reference where describe sysmaster tables? I have The informix Handbook, but it explains sysmaster ids v 7.x tables. Regards ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks all for your help. I reviwed link provided by Mike, but it has same limited information about sysmater, as all on-line information about informix. Art's tips has a wide information about it. I hope I can acomplish what I'm doing. Regards

If you cannot, post detailed questions about what you need and we'll help. Art S. Kagel ----- Original Message ----- From: Paulo Rafael Padilla Velazquez <ids@iiug.org> At: 3/15 14:16:45 Thanks all for your help. I reviwed link provided by Mike, but it has same limited information about sysmater, as all on-line information about informix. Art's tips has a wide information about it. I hope I can acomplish what I'm doing. Regards ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Thanks Art again. I detailed description about what I'm trying to get. I reviewed all information that you list in last response. I'm trying to catch what was last parsed sql sentence executed by a session and I can not get it trough sysmaster querying. I saw that querying syssqlstat I can get current running sql and this match with onstat -g ses <sesid> display, while it's running, but when session querying finished, my query doesn't show last parse sql sentence, but with onstat -g ses <sesid> I can see it. I reviewed sysmaster database and the only table that has sql sentence information is sysconblock. I queried sysconblock table, filtering session id, but last parsed sentence doesn't comes out. I realized that comes out other internal sql sentences, but, how does onstat –g ses <sesid> get last parsed sql sentence? Regards.

It should be getting it from sysconblk also (or at least the same in-memory data structures that are mapped to sysmaster:sysconblk). What version are you running and on what platform? In 10 & 11 you can turn on SET EXPLAIN for a session using onmode and capture all newly (explicitly or implicitely) prepared statements that way. Art S. Kagel ----- Original Message ----- From: Paulo Rafael Padilla Velazquez <ids@iiug.org> At: 3/22 14:44:17 Thanks Art again. I detailed description about what I'm trying to get. I reviewed all information that you list in last response. I'm trying to catch what was last parsed sql sentence executed by a session and I can not get it trough sysmaster querying. I saw that querying syssqlstat I can get current running sql and this match with onstat -g ses <sesid> display, while it's running, but when session querying finished, my query doesn't show last parse sql sentence, but with onstat -g ses <sesid> I can see it. I reviewed sysmaster database and the only table that has sql sentence information is sysconblock. I queried sysconblock table, filtering session id, but last parsed sentence doesn't comes out. I realized that comes out other internal sql sentences, but, how does onstat -g ses <sesid> get last parsed sql sentence? Regards. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Art, I found why it's happening when I was trying to query sysmaster. I have been using SSJE 6 to do my tests, but it gets connected to databases trough JDBC. And it uses other features for connections, which I really don't know yet. So, I did my tests directly from dbaccess and it's showing information that I'm seeking. We are using IDS 9.40.FC8 on SOLARIS 8 Thanks for your tracking

Ahh, great! Most likely culprit: the JDBC connection definition is actually looking at a different server. Art ----- Original Message ----- From: Paulo Rafael Padilla Velazquez <ids@iiug.org> At: 3/23 13:35:16 Hi Art, I found why it's happening when I was trying to query sysmaster. I have been using SSJE 6 to do my tests, but it gets connected to databases trough JDBC. And it uses other features for connections, which I really don't know yet. So, I did my tests directly from dbaccess and it's showing information that I'm seeking. We are using IDS 9.40.FC8 on SOLARIS 8 Thanks for your tracking ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.