Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    37

    Unanswered: How can I change the lock mode in DB2?

    Hi,
    I want to change lock mode to 'U'. For that what database configuration parameter I need to set?
    I had gone through the list of all database configuration parameters in "Administration Guide: performance" document,but I didn't find such parameter.
    So please any one of you help me?


    Thanks,
    Narender

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: How can I change the lock mode in DB2?

    You mean UR? If thats the case you can use db2 set isolation command.
    Please read ahead for implications. I don't know the details at this point.

    dollar

    Originally posted by pnarender
    Hi,
    I want to change lock mode to 'U'. For that what database configuration parameter I need to set?
    I had gone through the list of all database configuration parameters in "Administration Guide: performance" document,but I didn't find such parameter.
    So please any one of you help me?


    Thanks,
    Narender

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: How can I change the lock mode in DB2?

    I think it's change isolation level command, refer to this article:

    http://webdocs.caspur.it/ibm/web/udb....htm#HDRCMD025

    dollar
    Originally posted by pnarender
    Hi,
    I want to change lock mode to 'U'. For that what database configuration parameter I need to set?
    I had gone through the list of all database configuration parameters in "Administration Guide: performance" document,but I didn't find such parameter.
    So please any one of you help me?


    Thanks,
    Narender

  4. #4
    Join Date
    May 2003
    Location
    Amsterdam
    Posts
    65
    UR (uncommitted read) is not a database configuration parameter. It depends on what database access you use (ODBC, CLI, native SQL, etc.) how to spcify that you want UR.

    For CLI for instance: (from the CLI guide and reference)
    Uncommitted read is SQL_TXN_READ_UNCOMMITTED, can be specified with SQLSetConnectOption() and SQLSetStmtOption etc.
    Anton Versteeg
    IBM Netherlands

  5. #5
    Join Date
    May 2003
    Posts
    37

    Re: How can I change the lock mode in DB2?

    Dollar,
    Actually I want to lock a table in row level instead of whole table level. For that I want to change the lock mode to 'Update'. That's I need a database configuratiuon parameter to do that?


    Narender.




    Originally posted by dollar489
    You mean UR? If thats the case you can use db2 set isolation command.
    Please read ahead for implications. I don't know the details at this point.

    dollar

  6. #6
    Join Date
    May 2003
    Location
    Amsterdam
    Posts
    65
    No, you just open a cursor with "for update" and when you have fcetched the row you want to update, you can do either a update via the cursor or a non_cursor update.

    I think you better do some reading on DB2 application development, otherwise you might get into problems that could have been avoided.
    Anton Versteeg
    IBM Netherlands

  7. #7
    Join Date
    May 2003
    Posts
    37
    I am using "for update" and "concur_lock" as cursor options. my problem is that whether this locking will happen at tablelevel or rowlevel, if it happens at table level then how to make it row level, so as to improve performance.


    Narender

    Originally posted by antonv
    No, you just open a cursor with "for update" and when you have fcetched the row you want to update, you can do either a update via the cursor or a non_cursor update.

    I think you better do some reading on DB2 application development, otherwise you might get into problems that could have been avoided.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One can specify row or table locking level locking in the create/alter table statement. However, if you choose row level locking (default), DB2 can temporarily escalate the locking to table level if needs to, or if DB2 thinks that the escalation will improve performance on a particular transaction.

    One way to help limit lock escalation is to dramatically increase the amount of storage allocated in the LOCKLIST parameter in the database configuration. If DB2 runs out of storage needed for maintaining a large number of row locks, it may escalate to table level locking. As mentioned, another way to limit escalation is to use an isolation level of CS and update with a cursor.

    Of course, row level locking is time consuming and resource consuming for DB2. It will increase concurrency at the expense of individual transaction performance. Whether row level locking increases “overall” performance/throughput depends on the circumstances of the application.

Posting Permissions

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