Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to monitor when commit/rollback is executed?

    Hi,
    on DB2 Enterprise v9.5 fixpack 2 on Linux I have one application (all other applications are working without this problem) that according to application developer application does the following:
    1. start of transaction
    2. update table A
    3. update table B
    4. commit

    The application developer tells me that in some situation, transaction is rolled back, but it is not rolled back entirely. Application developer tells me that tables in database are updated that only reflect update A (step 2), but there is no update B (step 3). So application developer is convinced that rollback is only done for step 3 and not for both update steps.

    I suspect there is also commit done after step 2, but I am not an application developer, so I don't understand program code.

    My question: Is there any monitoring tool where I could monitor beside SQLs also a commit/rollback actions?
    Regards
    Last edited by grofaty; 10-07-09 at 08:52.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess the statement event monitor will show commits and rollbacks.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    DB2 has no "start transaction" command, so I am curious what your programmer is doing in this step. What language is the application written in?

    Andy

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    what about auto commit on in the calling appln.
    just a guess
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    @n_1, event monitor statements only shows SQL statements (select, update, delete, insert).

    @ARWinner, there is no "start transaction" command, I just wrote how app. developer is doing. I just want to write that no other command was executed before "update table A".

    @rahul_s80, no, autocommit is off.
    Last edited by grofaty; 10-07-09 at 09:47.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I am looking into db2 trace program. The biggest problem on using db2trc is huge amount of collected data. I have found out that filtering can be done using -m (mask) switch.

    So far I have made the following progress:
    Code:
    db2trc on -f raw -m "3.3.18.168.0"
    (reproduce the problem)
    db2trc dump dmp
    db2trc off
    db2trc format dmp fmt
    db2trc flow dmp flw
    There is an article: http://www.ibm.com/developerworks/da...dm-0409melnyk/
    I see at each record there is 5-dot option to filter out the desired records.

    I used the following: -m "3.3.18.168.0" but I am not sure if this is correct filtering option.

    Any one knows what is appropriate filtering for db2trace to get all SQLs that were executed and all rollbacks/commits that were executed.
    Regards
    Last edited by grofaty; 10-07-09 at 09:49.

  7. #7
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    I have found out that numbers in mask (filter) are changing according to DB2 version.

    Mask in my previous post ("3.3.18.168.0") was for version 9.7. In DB2 v9.5 fp2 correct mask is: "3.3.18.162.0".

    It looks IBM is renumbering mask after new features are added. It took me two hours to figure this out.

    It would be nice if there would be some documentation about db2trc mask option. Is there any - I have searched the web, but no info.
    Regards

  8. #8
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    back to the main topic. Using db2trc I have found out that when program finishes correctly one one commit is done, but when application is forced from db2cmd then 25 rollbacks are done inside of application. It is strange, so I have asked another programmer to check the code. I don't understand why there are 25 rollbacks needed if only one would be enough - it looks to me there is some spaghetti code.

    It looks there are 25 separate sections in code and when something goes wrong in one of them rollback is done. Rollback code is coded into each of 25 sections. Don't understand design, I would prefer to have only one section of code where only rollbacks would be defined, just like a commit is solved in code.
    Regards

Posting Permissions

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