Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    35

    Exclamation Unanswered: concurrent users access

    While doing load testing on our application using load testing tool open sta, We found that when 50-100 concurrent users access it , 20-30 records get inserted. The tool does not give any reason why the other transactions are rolled back. When I tried db2 list applications show detail, there were some locks waiting. I got a snapshot of the locks , changed the locking on tables used for reading to table level lock, But still there was no significant improvement. For the queries accessing only read only tables , I added the clause for read only. I increased statement heap size ,max appls , locksize and max locks according to the guidelines in the db2 documentation and discussing with some dbas. I changed the websphere connection pool timeout parameters for our datasource. Now there are no locks but out of 100 concurrent users hitting the application at a single point in time, only 40-50 are inserted. When I list applications, some are shown as connect completed and many are shown as uow waiting for a long period of time. I tried changing isolation level to Uncommited read using the command
    Change isolation to ur.But no improvement.

    Any Sugestions? Pl help as this is very critical

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    Tips

    Hi,

    The following can be done

    1. Ensure that the runstats/reorg is properly done and up to date.
    2. Indexes are properly defined on the table.
    3. Values of LOCKTIMEOUT, MAXLOCK and LOCKLIST are properly defined and adequate enough.
    4. A proper bind of the database is fne after you change the isolation level.

    Please also search for other posts on the same topic a lot has been discussed earlier on the same.

    Please also make it a habbit to post your operating system, database level and version with everypost.
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by manish21
    While doing load testing on our application using load testing tool open sta, We found that when 50-100 concurrent users access it , 20-30 records get inserted. The tool does not give any reason why the other transactions are rolled back. When I tried db2 list applications show detail, there were some locks waiting. I got a snapshot of the locks , changed the locking on tables used for reading to table level lock, But still there was no significant improvement. For the queries accessing only read only tables , I added the clause for read only. I increased statement heap size ,max appls , locksize and max locks according to the guidelines in the db2 documentation and discussing with some dbas. I changed the websphere connection pool timeout parameters for our datasource. Now there are no locks but out of 100 concurrent users hitting the application at a single point in time, only 40-50 are inserted. When I list applications, some are shown as connect completed and many are shown as uow waiting for a long period of time. I tried changing isolation level to Uncommited read using the command
    Change isolation to ur.But no improvement.

    Any Sugestions? Pl help as this is very critical
    Are you sure the problem is on the database, and not in the app?

    Are there any errors being returned to the app that may give you some clues?

    In websphere, changing the isolation level of the connection is tricky, it's supposed to be done in the resource-ref definition for the EJB... Websphere has a way of overriding anything else... it's been a bit of a nightmare for us, do a con.getTransactionIsolation() to verify the change really was effective.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by manish21
    I tried changing isolation level to Uncommited read using the command
    Change isolation to ur.But no improvement.
    Just curious - how did you manage this from Websphere? I don't think the CHANGE ISOLATION command works through the JDBC driver?
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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