sysmaster 's systrans
A user on IDS 11.50 asked what the tx_owner column in sysmaster:systrans means and whether systrans can show live transactions tied to sessions. Answers: tx_owner joins to rstcb/sysuserthreads by address (e.g. select us_sid, us_uid, T.* from sysuserthreads R, outer systrans T where R.us_address = T.tx_owner), noting one session can have several transactions. However, systrans rows are just allocated transaction structures, not necessarily open transactions; it also includes singleton statements and system activity. To spot genuinely open transactions, check the begin-work flag (the 'B' in onstat -x, roughly bit 0x400 in tx_flags).
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks, IDS11.50 FC8 I am quite interested in the table in sysmaster database: systrans. Its columns and contents are some thing like, tx_id 78 tx_addr 4599029344 tx_flags 540673 tx_mutex 4599091864 tx_logbeg 0 tx_loguniq 0 tx_logpos 0 tx_lklist 1186519744 tx_lkmutex 4599092032 tx_owner 4598849856 tx_wtlist 0 tx_ptlist 0 tx_nlocks 1 tx_lktout 300 tx_isolevel 2 tx_longtx 0 tx_coordinator tx_nremotes 0 My question is, what is tx_owner ? Can it be traced to a session id? I wish I can get some status list of real time running transactions associated with their sessions through this table , possible ? Thanks, Frank --bcaec555513c7b7c2b04c7532ec5
Something like
select tx_id
from systrans
where tx_addr = select us_txp
from sysuserthreads
where us_sid = SESSIONID)
Cheers
Paul
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FRANK
Sent: Wednesday, August 15, 2012 2:46 PM
To: ids@iiug.org
Subject: sysmaster 's systrans [28048]
Folks,
IDS11.50 FC8
I am quite interested in the table in sysmaster database: systrans.
Its columns and contents are some thing like,
tx_id 78
tx_addr 4599029344
tx_flags 540673
tx_mutex 4599091864
tx_logbeg 0
tx_loguniq 0
tx_logpos 0
tx_lklist 1186519744
tx_lkmutex 4599092032
tx_owner 4598849856
tx_wtlist 0
tx_ptlist 0
tx_nlocks 1
tx_lktout 300
tx_isolevel 2
tx_longtx 0
tx_coordinator
tx_nremotes 0
My question is, what is tx_owner ? Can it be traced to a session id?
I wish I can get some status list of real time running transactions
associated with their sessions through this table , possible ?
Thanks,
Frank
--bcaec555513c7b7c2b04c7532ec5
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
The tx_owner links to the sysmaster:rstcb table by the address column. There you will find the session's sid (session id). Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 15, 2012 at 3:46 PM, FRANK <yunyaoqu@gmail.com> wrote: > Folks, > > IDS11.50 FC8 > > I am quite interested in the table in sysmaster database: systrans. > Its columns and contents are some thing like, > > tx_id 78 > tx_addr 4599029344 > tx_flags 540673 > tx_mutex 4599091864 > tx_logbeg 0 > tx_loguniq 0 > tx_logpos 0 > tx_lklist 1186519744 > tx_lkmutex 4599092032 > tx_owner 4598849856 > tx_wtlist 0 > tx_ptlist 0 > tx_nlocks 1 > tx_lktout 300 > tx_isolevel 2 > tx_longtx 0 > tx_coordinator > tx_nremotes 0 > > My question is, what is tx_owner ? Can it be traced to a session id? > > I wish I can get some status list of real time running transactions > associated with their sessions through this table , possible ? > > Thanks, > Frank > > --bcaec555513c7b7c2b04c7532ec5 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba3fcba33b007c04c7534eb9
Thanks Paul and Art!
One more quesion: Does each row of systrans mean a Open( Running, not
commit yet) transaction ?
Thanks,
Frank
On Wed, Aug 15, 2012 at 3:51 PM, Paul Watson <paul@oninit.com> wrote:
> Something like
>
> select tx_id>
> from systrans
>
> where tx_addr = select us_txp
>
> from sysuserthreads
>
> where us_sid = SESSIONID)
>
> Cheers
> Paul
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> FRANK
> Sent: Wednesday, August 15, 2012 2:46 PM
> To: ids@iiug.org
> Subject: sysmaster 's systrans [28048]
>
> Folks,
>
> IDS11.50 FC8
>
> I am quite interested in the table in sysmaster database: systrans.
> Its columns and contents are some thing like,
>
> tx_id 78
> tx_addr 4599029344
> tx_flags 540673
> tx_mutex 4599091864
> tx_logbeg 0
> tx_loguniq 0
> tx_logpos 0
> tx_lklist 1186519744
> tx_lkmutex 4599092032
> tx_owner 4598849856
> tx_wtlist 0
> tx_ptlist 0
> tx_nlocks 1
> tx_lktout 300
> tx_isolevel 2
> tx_longtx 0
> tx_coordinator
> tx_nremotes 0
>
> My question is, what is tx_owner ? Can it be traced to a session id?
>
> I wish I can get some status list of real time running transactions
> associated with their sessions through this table , possible ?
>
> Thanks,
> Frank
>
> --bcaec555513c7b7c2b04c7532ec5
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--f46d04462d6c2e44d904c7539693
It will also include singleton transactions (an SQL statement executed
outside a formal transaction) and some system activity.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Wed, Aug 15, 2012 at 4:15 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Thanks Paul and Art!
>
> One more quesion: Does each row of systrans mean a Open( Running, not
> commit yet) transaction ?
>
> Thanks,
> Frank
>
> On Wed, Aug 15, 2012 at 3:51 PM, Paul Watson <paul@oninit.com> wrote:
>
> > Something like
> >
> > select tx_id> >
> > from systrans
> >
> > where tx_addr = select us_txp
> >
> > from sysuserthreads
> >
> > where us_sid = SESSIONID)
> >
> > Cheers
> > Paul
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > FRANK
> > Sent: Wednesday, August 15, 2012 2:46 PM
> > To: ids@iiug.org
> > Subject: sysmaster 's systrans [28048]
> >
> > Folks,
> >
> > IDS11.50 FC8
> >
> > I am quite interested in the table in sysmaster database: systrans.
> > Its columns and contents are some thing like,
> >
> > tx_id 78
> > tx_addr 4599029344
> > tx_flags 540673
> > tx_mutex 4599091864
> > tx_logbeg 0
> > tx_loguniq 0
> > tx_logpos 0
> > tx_lklist 1186519744
> > tx_lkmutex 4599092032
> > tx_owner 4598849856
> > tx_wtlist 0
> > tx_ptlist 0
> > tx_nlocks 1
> > tx_lktout 300
> > tx_isolevel 2
> > tx_longtx 0
> > tx_coordinator
> > tx_nremotes 0
> >
> > My question is, what is tx_owner ? Can it be traced to a session id?
> >
> > I wish I can get some status list of real time running transactions
> > associated with their sessions through this table , possible ?
> >
> > Thanks,
> > Frank
> >
> > --bcaec555513c7b7c2b04c7532ec5
> >
> >
> >
> ****************************************************************************
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --f46d04462d6c2e44d904c7539693
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae93404af8e94a104c753c574
Original Post: Thanks Paul and Art! One more quesion: Does each row of systrans mean a Open( Running, not commit yet) transaction ? Thanks, Frank Response: No. Each row in systrans is just an allocated transactions structure which may or may not be currently used by an existing open transaction or not. Every time a session is created it gets an rstcb structure allocated for it, and then the rstcb will allocate a transaction structure in the event that you perform some sort of work. But just because you have a transaction structure it does not mean that that structure is currently being use to track any currently open transaction. Jacques Renaut IBM Informix Advanced Support APD Team
Thanks a lot, Jacques !! A Little bit..... :-( if that row only means a static structure ( may or may not have a active running transaction) . Is it possible to determine if its corresponding transaction is ongoing or already done ? Thanks, Frank On Wed, Aug 15, 2012 at 4:48 PM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote: > Original Post: > Thanks Paul and Art! > > One more quesion: Does each row of systrans mean a Open( Running, not > commit yet) transaction ? > > Thanks, > Frank > > Response: > > No. Each row in systrans is just an allocated transactions structure which > may > or may not be currently used by an existing open transaction or not. Every > time a session is created it gets an rstcb structure allocated for it, and > then the rstcb will allocate a transaction structure in the event that you > perform some sort of work. But just because you have a transaction > structure > it does not mean that that structure is currently being use to track any > currently open transaction. > > Jacques Renaut > IBM Informix Advanced Support > APD Team > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae934098f240b7f04c7544793
original post:
Thanks a lot, Jacques !!
A Little bit..... :-( if that row only means a static structure ( may or
may not have a active running transaction) .
Is it possible to determine if its corresponding transaction is ongoing
or already done ?
Thanks,
Frank
Response:
Umm probably. The systrans output should map to onstat -x output and in onstat
-x output if you look at the doc there is a flag (in the 3rd position) of B
which indicates a begin work has been done. This should indicate that the
transaction is currently open. I think roughly once that B flag would be
cleared, that would mean the transaction had committed (or rolled back) and
was done. I believe onstat -x is getting that info from the tx_flags field and
formatting it. So I would think it would be easyish to get a test instance, do
a being work, and then do some work (need to make sure you do an
insert/delete/update to actually get something logged as we delay the begin
work until you actually do something), then find your session with onstat -x
and then look at the corresponding systrans output. I think the begin flag
value is going to be 0x400...so maybe using those bitval functions you could
check for that value in tx_flags if you are just looking for currently open
transactions.
Jacques Renaut
IBM Informix Advanced Support
APD Team
One thing to be careful of is that that a single user can have several
transactions.
select us_sid, us_uid, T.*
from sysuserthreads R, outer systrans T
where R.us_address = T.tx_owner
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 08/15/2012 12:55:20 PM:
> From: "Art Kagel" <art.kagel@gmail.com>
> To: ids@iiug.org,
> Date: 08/15/2012 12:57 PM
> Subject: Re: sysmaster 's systrans [28050]
> Sent by: ids-bounces@iiug.org
>
> The tx_owner links to the sysmaster:rstcb table by the address column.
> There you will find the session's sid (session id).
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly,
> implicitly, or by inference. Neither do those opinions reflect those of
> other individuals affiliated with any entity with which I am affiliated
nor
> those of the entities themselves.
>
> On Wed, Aug 15, 2012 at 3:46 PM, FRANK <yunyaoqu@gmail.com> wrote:
>
> > Folks,
> >
> > IDS11.50 FC8
> >
> > I am quite interested in the table in sysmaster database: systrans.
> > Its columns and contents are some thing like,
> >
> > tx_id 78
> > tx_addr 4599029344
> > tx_flags 540673
> > tx_mutex 4599091864
> > tx_logbeg 0
> > tx_loguniq 0
> > tx_logpos 0
> > tx_lklist 1186519744
> > tx_lkmutex 4599092032
> > tx_owner 4598849856
> > tx_wtlist 0
> > tx_ptlist 0
> > tx_nlocks 1
> > tx_lktout 300
> > tx_isolevel 2
> > tx_longtx 0
> > tx_coordinator
> > tx_nremotes 0
> >
> > My question is, what is tx_owner ? Can it be traced to a session id?
> >
> > I wish I can get some status list of real time running transactions
> > associated with their sessions through this table , possible ?
> >
> > Thanks,
> > Frank
> >
> > --bcaec555513c7b7c2b04c7532ec5
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --90e6ba3fcba33b007c04c7534eb9
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>