How to monitor when commit/rollback is executed?

grofaty · #1 (**permalink**) 10-07-09, 06:50

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

n_i · #2 (**permalink**) 10-07-09, 08:03

I guess the statement event monitor will show commits and rollbacks.

ARWinner · #3 (**permalink**) 10-07-09, 08:10

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

rahul_s80 · #4 (**permalink**) 10-07-09, 08:16

what about auto commit on in the calling appln.
just a guess

grofaty · #5 (**permalink**) 10-07-09, 08:17

@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.

grofaty · #6 (**permalink**) 10-07-09, 08:42

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

grofaty · #7 (**permalink**) 10-12-09, 01:44

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

grofaty · #8 (**permalink**) 10-12-09, 01:50

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

Internet Services

Web Development

Digital Marketing

Consumer Tech