Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2015
    Posts
    17

    Unanswered: Locklist & maxlocks - automatic

    Hello,

    The below points may be basic but would be of great help it they can be answered.

    1. We have the parameters as
    LOCKLIST = AUTOMATIC (4096)
    MAXLOCKS = AUTOMATIC (98)
    What does this signify? Are LOCKLIST & MAXLOCKS set to AUTOTIC or are they set to the values in braces? If they are set to AUTOMATIC, what is the maximum limit of LOCKLIST that system would assign to LOCKLIST?

    2. I read that MAXLOCKS is the maximum %age of LOCKLIST memory that can be allocated to an application. So, my question is what an application is? In our case we have a lot of users accessing the database oneline through an application that has different modules. We do have a lot of reports that are run in parallel by same set of users. So what would be an application in this case? Would it be one user profile? The application is web-enabled, so does an application here would mean one window opened by one user.

    3. I ask all this because it seems locks are getting escalated to table locks and making some users wait long till they are able to have their part of work completed.

    Thanks in advance.

    Thanks & Regards,
    Dhiraj

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated in doc : automatic means automatic growth. (xx) is current allocation
    memory is limited - db heap - instance memory - machine memory
    when locklist grows their is a risk for lock-escalation (look in db2diag.log) reduce the nbr of locks by intermediate commit - isolation level - reducing answer set
    application = connection = luw
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2015
    Posts
    17
    Thanks for quick reply. Can you please let me know if you think that increasing the LOCKLIST parameter or any other parameter would be of help. Isn't the maxlocks parameter too high - 98%? I ask because on application side, we have made changes to change isolation levels and add commits/rollback wherever possible. So, trying to see if we can change any system paratemer.

    Thanks & Regards,
    Dhiraj

  4. #4
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    LOCKLIST can be increased. What is total memory on server and how many databases are running on the server?

    Use AUTOCONFIGURE command to get initial set of configuration settings based on your application environment. Using the suggested values from AUTOCONFIGURE command, you need to fine tune to meet your environment. See the following link for more details.

    https://www-01.ibm.com/support/knowl.../r0008960.html

    Satya...

  5. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    Yep, some info on the system resource would be helpful. Have you set locktimeout? As Guy said, see if the apps are behaving badly and not committing regularly. Or just throw a truck load of memory at the server. Databases love memory!
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

  6. #6
    Join Date
    Sep 2015
    Posts
    17
    Thanks all for the responses! We have set LOCKTIMEOUT to -1. We have 100 GB RAM and only one database running on it. We plan to raise RAM to 128 GB. I had few more questions.

    1. We recently upgraded to 10.5 from 9.7. Does an upgrade from 9.7 to 10.5 impact the locking in anyway? Is there any difference in the way this is dealt in the 2 versions? Could it be that
    2. Would increasing RAM make the system tune it to a higher LOCKLIST value?
    3. Since presently the settings are for self-tune, would it be worth trying to make the change to LOCKLIST parameter? I ask because it shouldn;t have -ve impact.

    Thanks & Regards,
    Dhiraj

  7. #7
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Locklist 4096 for 100GB system seems too small. On 16GB system, we have locklist 40000. What is the output of autoconfigure? You can run it with apply none option which will suggest the new values with out actually making changes.

  8. #8
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    I agree that it's small. We have auto on locklist and it's currently sitting at 524288 in a 90Gb instance. And it will get bigger. I also wouldn't be running locktimeout -1 on a production system unless you want to monitor locks all day
    You can monitor any change in the lock manager memory using db2top. You can also alter the locklist but leave it automatic
    Andy

    "All parts should go together without forcing. You must remember that the parts you are reassembling were disassembled by you. Therefore, if you can’t get them together again, there must be a reason. By all means, do not use hammer.” — IBM maintenance manual, 1975 "

Posting Permissions

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