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.
    – mustaccio
    Commented Mar 11, 2015 at 14:11
  • What exactly are you hoping to learn with this information?
    – Ian Bjorhovde
    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 :-)
    – Dror
    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.
    – Dror
    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.
    – Chris Aldrich
    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 :-)
    – Dror
    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 :-(
    – Dror
    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.
    – Chris Aldrich
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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