onstat -x and sysmaster:systrans
Jacob Salomon asked which SMI table gives the begin_logpos and current logpos shown by onstat -x, as systrans did not seem to. Markus Holzbauer supplied a sysmaster query joining sysrstcb and systxptab using logbeg, logbeg_pos, loguniq and logpos. Jacob extended it with session columns from syssessions. Andreas Legner noted undocumented tables are described in sysmaster.sql. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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 KbytesTransactions
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!
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
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
------------------------------
Markus Holzbauer
------------------------------
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
------------------------------
+-----------------------------------------------------------+
| I am pleased to report that I had no problems today. |
| I had only issues, opportunities, challenges and valuable |
| learning experiences. |
+------------------------------------------ Jacob S --------+
------------------------------
While possibly not documented on the internet, you can still learn about these (pseudo) tables and views in $INFORMIXDIR/etc/sysmaster.sql ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------