Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    427
    Provided Answers: 1

    Unanswered: Configuration locklist and maxlocks in db2 v10.0.5 fxp 8

    Hi colleagues,
    I have a db2 v10.0.5 fxp 8 in aix, in db2diag.log always there is messages lock scalation, the parameters locklist is set in 8192 and maxlocks is set in 10.

    What will to do for solve this problem?
    Thank you for advanced.
    DBA DB2 for LUW

  2. #2
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    If you are using STMM, then set them both to AUTOMATIC.

    Andy

  3. #3
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    427
    Provided Answers: 1
    Thank you for you quick answer, la parameter SELF_TUNING_MEM is OFF.

    Best regards.
    DBA DB2 for LUW

  4. #4
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    Then is all depends on how much memory you have left on the server. Either you turn on STMM, or you increase locklist and maxlocks.


    Andy

  5. #5
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    427
    Provided Answers: 1
    In this server there is 20 gb physical memory, but there are eight instance, all instances there is one database, the parameters INSTANCE_MEMORY and DATABASE_MEMORY is automatic.
    This is the error in db2diag.log
    MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application
    is named "db2jcc_application", and is associated with the workload
    name "SYSDEFAULTUSERWORKLOAD" and application ID
    "192.168.49.132.33980.170508070018" at member "0". The total number
    of locks currently held is "25434", and the target number of locks to
    hold is "12717". The current statement being executed is "delete from
    PLN.PRDP_RESOURCE_DEPS where (( PLN.PRDP_RESOURCE_DEPS.PPLN_ID=?))
    and PJOR_ID in (select PJOR_ID from PLN.PRDP_RESOURCE_DEPS where ((
    PLN.PRDP_RESOURCE_DEPS.PPLN_ID=?)) fetch first 10000 rows only)".
    Reason code: "1"

    Thank you.
    DBA DB2 for LUW

  6. #6
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    What does this line give you that you do not get by leaving it out? It is what is causing all of the locks as well, I would bet.

    Code:
     and PJOR_ID in (select PJOR_ID from PLN.PRDP_RESOURCE_DEPS where (( 
     PLN.PRDP_RESOURCE_DEPS.PPLN_ID=?))

  7. #7
    Join Date
    Jan 2003
    Posts
    4,310
    Provided Answers: 5
    20GB is not very much memory to share between 8 instances and databases. You really cannot do much to alleviate this situation. Your lock list is small, and I do not see how you can increase it any. You could try increasing maxlocks, but I would not be optimistic that that alone will alleviate the situation.

    To prevent lock escalations, you have two options.
    1) make the transactions smaller so that they do not lock lots of rows
    2) increase locklist and maxlocks

    My guess is that neither option will work for you, so you are going to have to live with the escalations.

    Andy

  8. #8
    Join Date
    May 2010
    Location
    India
    Posts
    90
    Provided Answers: 2
    What is the workload on the database? Are you expecting 10 transactions running same time and each transaction holding lot of locks? If not, you can increase MAXLOCKS.

    Satya...

Posting Permissions

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