A while ago I discovered a transaction that had been active for a long
time. The statement where against a infrequently accessed table, and
caused no problem except that the transaction log continued to be
active. So I started looking at how to detect such things for the
future. What does it mean if sysibmadm.SNAPAPPL.UOW_STOP_TIME is
null? Is this an open transaction, or can it be null for other
reasons?

Thanx
/Lennart

On 3/5/10 2:50 AM, Lennart wrote:

UOW Stop Time is the time when a COMMIT or ROLLBACK was issued for a
particular transaction. If it is null, then yes, you're correct,
the transaction is still active.

On 5 mar, 21:36, Ian <ianb... (AT) mobileaudio (DOT) com> wrote:
Thanks, that was what I suspected (and feared). I forced these
transactions yesterday, and there aren't any new ones (yet?). In case
someone is interested, here is the qeury I've used:

select x.agent_id, x.uow_start_time, substr(y.stmt_text,1,200),
y.stmt_start, y.stmt_stop
from sysibmadm.snapappl x join sysibmadm.snapstmt y
on x.agent_id = y.agent_id
where x.uow_stop_time is null
and timestampdiff(8,CHAR(current_timestamp - x.uow_start_time)) >
8
order by x.uow_start_time;

/Lennart




select
x.agent_id,
UOW_START_TIME, substr(stmt_text,1,200) from sysibmadm.SNAPAPPL x
join sysibmadm.snapstmt y on x.agent_id = y.agent_id where
UOW_STOP_TIME is null and TIMESTAMPDIFF(2,CHAR(current_timestamp -
UOW_START_TIME)) > 8 order by UOW_START_TIME