0

How can I get list of open (uncommitted transactions) in DB2 LUW?

Or just the oldest transaction id?

Basically, I need to get the first change of the oldest open transaction. I found APPL_ID_OLDEST_XACT in SYSIBMADM.SNAPDB view, but it tells me only the application id and I could figure out how to get to the actual LSN/LRI.

I was suggested to use db2pd -transactions, but I can only run it from server side. Is there a solution that I can run from client side?

Improve this question
3
  • It's not clear what you want: transaction ID or LSN? These are different things. Commented Mar 11, 2015 at 14:11
  • What exactly are you hoping to learn with this information? Commented Mar 11, 2015 at 15:56
  • Basically i need the oldest LSN that is not committed (open transaction). The easiest way to get that will be very much appreciated :-) Commented Mar 15, 2015 at 15:41

2 Answers 2

Reset to default
2

Assuming you have a newer version of DB2 (10.1 or higher, although I want to say a few of these are in 9.7). You have the Monitor views and functions.

The MON_CURRENT_SQL view and the MON_GET_ACTIVITY table function look like they could possibly be what you are after.

Improve this answer
9
  • Thanks for the detailed answer, but, i cannot find in these views the open transaction list and/or transaction id. Commented Mar 11, 2015 at 14:11
  • @Dror those were just recommendations. If you take a peek at the views and functions lists that I linked to, you may find the exact one you are looking for. Commented Mar 11, 2015 at 19:55
  • Hi Chris, thanks for your recommendations but i could not find what i was looking for. Basically i need the oldest LSN that is not committed (open transaction). The easiest way to get that will be very much appreciated :-) Commented Mar 15, 2015 at 15:43
  • Anybody? i still could not find a way to get the list of open transactions or something else that will help me :-( Commented Mar 22, 2015 at 16:10
  • If it runs server side only, perhaps you need to use something like PuTTY to remote to the server to run it. Commented Mar 23, 2015 at 12:44
1

Is this what you want?

select OLDEST_TX_LSN 
from table(sysproc.MON_GET_TRANSACTION_LOG(-1))
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.