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?

  • It's not clear what you want: transaction ID or LSN? These are different things. – mustaccio Mar 11 '15 at 14:11
  • What exactly are you hoping to learn with this information? – Ian Bjorhovde Mar 11 '15 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 :-) – Dror Mar 15 '15 at 15:41
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.

  • Thanks for the detailed answer, but, i cannot find in these views the open transaction list and/or transaction id. – Dror Mar 11 '15 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. – Chris Aldrich Mar 11 '15 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 :-) – Dror Mar 15 '15 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 :-( – Dror Mar 22 '15 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. – Chris Aldrich Mar 23 '15 at 12:44
1

Is this what you want?

select OLDEST_TX_LSN 
from table(sysproc.MON_GET_TRANSACTION_LOG(-1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.