Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Cool Unanswered: Getting Transaction id inside procedure

    How can I access or get the transaction id or some thing that can help me group multiple DML's together? A transaction which can have mutiple inserts ,mutiple updates and multiple deletes. I want group all these operation together.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Each connection can only have one transaction at a time. A transaction is everything between one commit/rollback to the next.

    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you really need a transaction ID, you may want to have a look here: Transaction IDs in DB2 Note that the article is a few years old and you may have to adjust the log reading/scanning part.

    What exactly do you want to do? I'm asking because usually the request to have some sort of transaction ID indicates a problem in your application design. You should be able to do without something like this. It would be much safer, too.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jan 2012
    Posts
    3
    Thanks Kunt and Andy for the replies.

    I have a table which stores group and the linked operation. When ever the operation linked to group changes. The system deletes all the rows for that group and reinserts all the new changes. I am using triggers to capture all the changes in this table. My trigger inserts into a temp table when ever there is insert to the original table. This is working as expected. I need something (transaction id) to group inserts for a group together so that i can put sequence as in 1,2,3 etc.

    Example
    Suppose Group A can have 1,2,3,4 operations. When the group is created the trigger will insert four rows for group into temp table. I want something using which I can tie these rows together and put a change count 1. Suppose later the group is changes to have five operation 1,2,3,4,5. The trigger will again insert these five rows into temp table. Again here I want to put a change count of 2 to this group. The logic should apply for all the group changes.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The combination of the application ID and the UOW ID should be sufficiently unique for your purpose. Something like:

    Code:
    select 
     c.application_id, u.uow_id 
    from 
     sysibmadm.mon_connection_summary               c
     ,sysibmadm.mon_current_uow                            u 
    where 
     u.application_handle = c.application_handle         and 
     c.application_id = application_id()

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You don't need a transaction ID. If your temp table is a a real temp table (declared or created), then the trigger (which is executed in the scope of the current transaction) will only see the rows it puts into the temp table itself. It won't see data from other transactions. You should create/declare the temp table in such a way that it discards rows on ROLLBACK and COMMIT, of course.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •