Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Any DB2 parameter to limit transaction ?

    Hi Guys,

    There has been an issue with our applications in develpment environment.
    A Staging server is locking GAME_SERVER_STATUS table. On investigation I found that an update on this table is schedulled to be every 3 Secs. This is done by Gaming Application Server.

    When I see a lock on the table there are two updates from same server. Does that mean the first update request did not finish in 3 seconds.

    That keeps happening. The development team wants me to terminate the first update thread (agent) if it takes more than 2 seconds.

    So, is there a way to terminate a transaction if it takes longer to execute ?


    Regards

    DBFinder

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think you need to fix the problem, and not the symptom. The symptom is that the update is not terminating soon enough. Killing it will not alleviate the problem. You need to determine why the update is lasting longer than necessary. There can be several reasons. It could be the network. It could be the update statement. It could be that the application is not committing the update.

    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Alright,
    I am working on this. The question I asked is beacuse they want me to do that; which I refused to do officially. However, I want to learn, if there is any way to do that. Definitely, you may trust me that as a DBA I would not do that.
    As an Application Developer I do believe that they are not using commit at the end.

    Regards

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no parameter that I can think of that could do this. You would have to write a script that constantly loops looking for an application that is holding a lock for too long, and then kill (force) that application. I see far too many potential problems from this "solution".

    Andy

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    That's why it is not available officially. By the way the server is in STAGING envoirnment, and the manager has sent me a request to setup a cron job that will kill this thread.
    I have also requested that java code to see if i can reccomend any change to it.

    Thanks for your support.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    That's what the "Workload monitor" is for.
    http://publib.boulder.ibm.com/infoce.../c0052594.html

    It can be used to assign certain resource limits to "user groups" and based on the resource comsumption the transaction could e.g. be terminated.

    It is not easy to setup and I think it is an additional option in the enterprise edition that you have to pay for.
    http://publib.boulder.ibm.com/infoce.../c0052600.html

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by ARWinner View Post
    I think you need to fix the problem, and not the symptom. The symptom is that the update is not terminating soon enough. Killing it will not alleviate the problem. You need to determine why the update is lasting longer than necessary. There can be several reasons. It could be the network. It could be the update statement. It could be that the application is not committing the update.

    Andy
    Hi Andy,

    The problem has been identified. I found that some connections were being dropped amidst the transactions. I sent this observation to IMS team which further analyzed network devices. They found some settings on firewall needed to be set according to server's response time.
    At the same time I offered one server from my personal lab that was accepted and put to test. This server worked good with old and new firewall settings,IMS reported.
    The killer loop that I created worked very nice. Any lock that persisted 20 sec on one main table was forced affectively.This table X lock was held by application server while maintaining connection pool. So this temporary solution proved life saver to other applications. This was needed as a pain killer. Please note that this kind of solution was applied to the STAGING server on developers' demand because the server belonged to developers solely.

    This will be tested again when I will put original server back, somewhere next week.

    Thanks everybody for support.

    DBFinder

Posting Permissions

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