Several of the SMI tables are not documented and not officially supported. These could change in future releases. Two additional unsupported tables I have found helpful are systrans and syssqexplain.
Three of the fields in systrans are very helpful to determine what logical log number a transaction began in, and the current logical log number in use by a transaction.
Key systrans fields:
Column Data Type Description tx_id integer pointer to transaction table tx logbeg integer transaction starting logical log tx_loguniq integer transaction current logical log number
This can be used to create a script to determine what logical logfiles have active transactions. The output of this will tell you what logical logs are free and available for reuse. This first script lists all user transactions and what logs they are using.
--txlogpos.sql
select
t.username,
t.sid,
tx_logbeg,
tx_loguniq,
tx_logpos
from systrans x, sysrstcb t
where tx_owner = t.address
SQL Output
Username sid tx_logbeg tx_loguniq tx_logpos
informix 1 0 16 892952
informix 7 0 0 0
informix 8 0 0 0
lester 53 0 0 0
informix 12 0 0 0
lester 51 14 16 0
---------------------------------------------------
This shows that my logical logs numbered 14 to 16 are in use by transactions. Another helpful use of this view is to summarize the transactions by logical logs. This next script show my transaction status by logical log.
--logstat.sql database sysmaster; --select transaction data into a temp table select tx_logbeg, tx_loguniq from systrans into temp b; --count how may transactions begin in each log select tx_logbeg, count(*) cnt from B where tx_logbeg > 0 group by tx_logbeg into temp C; --count how many transactions currently are in each log select tx_loguniq, count(*) cnt from B where tx_loguniq > 0 group by tx_loguniq into temp D; --join data from counts with syslogs select uniqid, size, is_backed_up, -- 0 = no, 1 = yes log is backed up is_archived, -- 0 = no, 1 = yes log is on last archive c.cnt tx_beg_cnt, d.cnt tx_curr_cnt from syslogs, outer c, outer D where uniqid = c.tx_logbeg and uniqid = d.tx_loguniq order by uniqid
uniqid size is_backed_up is_archived tx_beg_cnt tx_curr_cnt 10 500 1 1 11 500 1 1 12 500 1 1 13 500 1 1 14 500 1 1 15 500 1 1 16 500 0 1 1 2
This shows that all logs are backed up except the current one, and it has two active transactions.
Have you ever wanted to run a query to see what your users were doing? The view syssqexplain contains some of the data from a user's session, including the SQL that they are currently executing. Try this query on your system sometime to see your user's SQL.
--syssql.sql select username, sqx_sessionid, sqx_conbno, sqx_sqlstatement from syssqexplain, sysscblst where sqx_sessionid = sid
username lester sqx_sessionid 55 sqx_conbno 2 sqx_sqlstatement select username,sqx_sessionid, sqx_conbno, sqx_sqlstatement from syssqexplain, sysscblst where sqx_sessionid = sid username lester sqx_sessionid 51 sqx_conbno 0 sqx_sqlstatement update items set total_price = 300 where item_num = 1 ---------------------------------------------------