An application which used to finish in 2 hours is taking 4 hours to finish. These extra two hours are caused because the application is staying more time in commit active state.

Any suggestions to solve this issue would be appreciated

Could be related to the size of the history file:
Commit Active problem solved (db2rhist.asc) - Database Forum

Thanks Bella for you help. But the problem is I cannot run a truss on a single thread as db2 is thread based now. I would be glad if someone could help me determine how to run a trace or a truss on a single thread id so that I can determine what exactly is holding from that application to commit.

I guess the point was that you could look at the size of db2rhist.asc and prune the database history if the file looks too big.

Yes, I did prune the history file but still the application stays in commit active state. If I could run a trace on the thread ID or EDU ID which is in commit active state, I should be able to figure out what exactly is resisting it from being committed. Is there any way to run a trace on that ??

I don't think you can do truss at the thread level, you need to provide the pid (-p <pid of db2sysc>). But you don't know why the application is staying a long time in commit active... could be due to a large history file (you pruned it), it could be that the logger is waiting for something or some other reason. So, I think you need the information for all threads to start with. db2 call stacks could also tell what is happening. You can capture them using "db2pd -stacks" or "db2pd -stack <edu id>" (for a specific thread). You may want to open a pmr...

You didn't mention your DB2 version. In 9.5 you can try db2pd with the -edus and -latches options - may be it will give you some hints.

Thanks Bella and Nick, db2pd -latches was helpful. I could see the culprid EDU ID in holding state. I would appreciate if someone could help me in interpreting the output of db2pd -latches

$ db2pd -latches

Database Partition 0 -- Active -- Up 114 days 09:03:38

Latches:
Address Holder Waiter Filename LOC LatchType
0x0700000250B69A00 15937 0 sqlpxcm1.C 206 SQLO_LT_SQLP_TENTRY__tranEntry
0x07000002CA484D88 31746 0 sqlbpacc.C 1562 SQLO_LT_SQLB_POOL_CB__readLatch
0x07000003494B76C0 31746 0 sqlbilatch.C 1366 SQLO_LT_SQLB_POOL_MAP_CB__range_latch
0x0700000252E6BA50 57354 0 sqlbbuffers.C 6199 SQLO_LT_SQLB_DIRTY_LIST_SET__walkLatch
0x07000002512EB100 57354 0 sqlbbuffers.C 6186 SQLO_LT_SQLB_BufferPool__prevBPDCachingLatch
0x0700000252E6B7F8 57354 0 sqlbbuffers.C 6199 SQLO_LT_SQLB_DIRTY_LIST_SET__appendLatch
0x07000002CB8695C0 57354 0 sqldalter.C 6755 SQLO_LT_SQLD_TCB__datExist


The holder 57354 is the EDU id of the agent which is in commit active state.

You need more info (ie. several call stacks) in order to see what each EDU is doing and this info is something for DB2 support to interpret.

Based on the above info:
15937 is committing a transaction.
31746 is reading pages into the bufferpool.
57354 is most likely flushing dirty pages from the bufferpool and/or truncating a temporary table.

I alredy opned a PMR. But thanks for all your help.

Finally i figured out the reason why the application is staying longer time in commit active state. It is because auto_runstats and auto_stmt_stats were turned on. From the time these parameters were turned on the process which used to finish in 35 minutes was taking 400-700 minutes. I am currently running V9.5 FP2a. Once I turned it off, the application started taking its normal 35 minutes of time. So folks plz be aware of V9.5 FP2a and the auto_maintenance future being turned on. I just reported IBM about this. Hopefully they will come out with any solution for this.