Some Unsupported Extras
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.
User transactions: systrans
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
SQL Output
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.
User queries: syssqexplain
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
SQL Output
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
---------------------------------------------------