Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2005

    Unanswered: db2 Performance Prolems

    Hi ALL,

    I have 2 questions

    1) When we bind the application to the database we specify an isolation level of UR (see statement below.)

    db2 bind $i qualifier awqm dynamicrules bind sqlerror continue validate run isolation UR blocking ALL

    My understanding is that means that ALL static SQL statements in the code will be executed using the WITH UR clause even if not specified in the actual select statement. Is there any case where this is not true ?

    And also does this apply to both cursors and Select into statements in the code ?

    2) Is there a way to monitor locks on a table and tell which SQL statement is causing the lock ?

    Thanks in Advance.
    Your Help is appreciated.

  2. #2
    Join Date
    May 2003
    1. Yes, that should be true. However this only applies to select statements, and not for select for update, insert, update, or delete.

    2. You can do a snapshot for locks. See the Command Reference manaul for get snaphsot. You must first turn on the appropriate monitor switches (for locks, statements, and timestamp) in the DB2 instance configuration (db2 get dbm cfg).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Oct 2001
    Just a continuation to this topic:

    DB2 V8.1 has RR as it's default isolation level. This will hold good for all the objects (User and system catalog + the packages). In a scenario where i have to fire same procedure from multiple threads the RR isolation creates locks even for the data being read or referenced. RR also leads to lock escalation if LOCKLIST is not sufficient. So i wanted to move to CS or RS.

    Can someone please tell me how i can force some database to use the CS or RS isolation level?

    The DB2_RR_TO_RS=YES works only for user tables and not the system tables. I have seen commands like "change isolation level" and "set isolation level" but they work only for session level.


Posting Permissions

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