WOW!  I feel so anachronistic!  Here folks are discussing release 15 and "large tables" and I'm back in release 12!

I am almost certain Lester K must have posted a query about this but I can't find it.  So I ask  the community:

Here's a sample of output from onstat -x, filtered to view only live transactions and minus exotic columns

onstat -x | awk '$5 != "-" {print}' | head -12

IBM Informix Dynamic Server Version 12.10.FC9W1X4 -- On-Line -- Up 12:09:07 -- 223444992 Kbytes

Transactions
                                                                                           est.
address          flags userthread       locks  begin_logpos      current logpos    isol    rb_time 
1823f57a8        A-B-- ef64f6af8        41     3093500:0x8535018 3093501:0x87046f4 DIRTY   00:00:08
1823fcff8        A-B-- b9f252948        3      3093496:0xc584290 3093496:0xc5842c8 DIRTY   00:00:00
1823ff5c8        A-B-- 8f9f5d788        4      3093500:0x8c75240 3093500:0x8c75278 DIRTY   00:00:-1
1c1641088        A-B-- 9f59ce8d8        3      3093498:0x3a9a018 3093498:0x3a9a050 DIRTY   00:00:00
1c164c348        A-B-- 333c378b8        84     3093499:0x2e5d380 3093501:0x9206188 DIRTY   00:02:20
1d3373618        A-B-- ef65247d8        91     3093501:0x92b1018 3093501:0x98d86d8 DIRTY   00:00:00

(Too bad that designating it as code does not force a monospace font.)

Note the nice format of columns begin_logpos and "current logpos" the the unique log number (decimal) and the position within the log, in hex. In what SMI table could I have found this level of detail?  systrans.tx_logbeg and systrans.x_logpos  don't seem to cover it. And in any case, if all that is in the single integer, how would I separate the loguniq from the position within the log?  And, BTW, systrans seems not to be documented in the Admin Reference so I likely should not be looking there.

What's more annoying: The section that maps onstat option to SMI tables says nothing about onstat -x.  This is why I am trying to do this on my own.

Some time ago I wrote transactions.pl that depended entirely on the output of onstat commands (-x, -u, -g ses) but now I'd like to mimic that action with SMI alone.

Ideas, anyone?  Thanks!

Hello Jacob,

maybe this sysmaster sql is what you need:

select a.sid,
       b.nlocks,
       a.upf_logspuse::decimal(12,0) lgspusd,
       (b.logbeg||':'||hex(logbeg_pos))::char(16) begin_logpos,
       (b.loguniq||':'||hex(logpos))::char(16) cur_logpos,
       b.rb_time
 from sysrstcb a,systxptab b
  where a.address = b.owner
    and b.logbeg > 0
 order by 2;

Cheers,
Markus

Thank much, Markus; this is beautiful art.  And I see systxptab isn't documented either.  Grumble...

In any case, I enhanced your gift with some more of the information I wanted to display:

select tx.txid,
       tcb.sid,
       ses.username,
       ses.hostname,
       ses.tty,
       tx.nlocks,
       tcb.upf_logspuse::decimal(12,0) lgspusd,
       (tx.logbeg||':'||hex(logbeg_pos))::char(16) begin_logpos,
       (tx.loguniq||':'||hex(logpos))::char(16) cur_logpos,
       tx.rb_time,
       ses.feprogram
  from sysrstcb tcb, systxptab tx, syssessions ses
 where tcb.address = tx.owner
   and tcb.sid = ses.sid
   and tx.logbeg > 0
 order by 8;

Order by beginning log so I see the oldest transactions first. I will be wrapping this in a Perl script that unloads to a flat file and formatting it nicely with a heading.  (Sadly, very few of my servers have the Perl package DBI installed.)

-- Jacob S

select a.sid,       b.nlocks,       a.upf_logspuse::decimal(12,0) lgspusd,       (b.logbeg||':'||hex(logbeg_pos))::char(16) begin_logpos,       (b.loguniq||':'||hex(logpos))::char(16) cur_logpos,       b.rb_time from sysrstcb a,systxptab b  where a.address = b.owner    and b.logbeg > 0 order by 2;

While possibly not documented on the internet, you can still learn about these (pseudo) tables and views in $INFORMIXDIR/etc/sysmaster.sql 😉

select a.sid,       b.nlocks,       a.upf_logspuse::decimal(12,0) lgspusd,       (b.logbeg||':'||hex(logbeg_pos))::char(16) begin_logpos,       (b.loguniq||':'||hex(logpos))::char(16) cur_logpos,       b.rb_time from sysrstcb a,systxptab b  where a.address = b.owner    and b.logbeg > 0 order by 2;