Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Posts
    272

    Unanswered: Applicaiton staying long time in commit active state

    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

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Could be related to the size of the history file:
    Commit Active problem solved (db2rhist.asc) - Database Forum

  3. #3
    Join Date
    Jun 2009
    Posts
    272
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jun 2009
    Posts
    272
    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 ??

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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...

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jun 2009
    Posts
    272
    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.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.
    Last edited by db2girl; 01-08-10 at 01:28.

  10. #10
    Join Date
    Jun 2009
    Posts
    272
    I alredy opned a PMR. But thanks for all your help.

  11. #11
    Join Date
    Jun 2009
    Posts
    272
    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.
    Last edited by blazer789; 01-12-10 at 14:05.

Posting Permissions

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