Detecting long transactions

SQL to detect how long a transaction has been open and displays all transactions that have less than a percentage amount left of logs before going into long transaction.

database sysmaster;
set isolation to dirty read;
select a.sid, b.longtx, b.logbeg, c.cf_effective ltxhwm,
d.cf_effective logfiles, e.uniqid currlog,
round(((e.uniqid - b.logbeg)/d.cf_effective)*100) pctused
from sysrstcb a, systxptab b, syscfgtab c, syscfgtab d, syslogs e
where a.address>0
and a.sid>0
and b.logbeg>0
and c.cf_name="LTXHWM"
and d.cf_name="LOGFILES"
and e.is_current=1
and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10
and a.address=b.owner
order by pctused desc

The query will display the following columns:

sid - session id of the user thread
longtx - 1 if in long transaction already
logbeg - begin log of this transaction
ltxhwm - long transaction high water mark set in onconfig
logfiles - number of logical logs
currlog - current log used by the instance
pctused - percentage of the total number of logs (i.e. logfiles) from logbeg to currlog of this transaction

The output will be displayed in the order of pctused. Only those transactions that have 10% or less amount of logs remaining before reaching the LTXHWM will be displayed. You can change this condition by changing the following line:

and c.cf_effective - round(((e.uniqid - b.logbeg)/d.cf_effective)*100) <= 10 (change to 20, 30, etc, this is the percentage left before reaching LTXHWM)