Results 1 to 15 of 15

Thread: Isolation Level

  1. #1
    Join Date
    Jan 2004
    Posts
    18

    Question Unanswered: Isolation Level

    First off: I am relatively new to DB2, having worked on Oracle for 10 years.

    Second: I have looked around the forums, etc for an answer!

    Quite simply, how can you check what the current ISOLATION LEVEL is on your database?

    I know you can change it using AUTOCONFIG, but how can you check what it is before you change it?

    From what I have read, this seems to be somewhat of an enigma.

    I am using DB2 UDB 8.1 EEE on NT.

    Cheers,

    Brian.

    Brian-dot-Murray-at-FINEOS-dot-com

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

    Nope

    Hi Brian

    Unfortunately there is no way by which you can see the isolation level set at the database level.

    There has been many a discussion on the mentioned topic and you can refer those threads for a better clarity.

    Cheers

    Nitin
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Sep 2003
    Posts
    237
    What about db2set DB2_RR_TO_RS=YES ? Does it not set Isolation level to RS at Instance level? There are so many places, you can override defaults e.g at general SQL level or individual SQL level. If you are using an application package, package should recommend the settings.
    mota

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Here is some information that I previously posted about isolation level. You might be able to see the default isolation level, but that is no guarantee that a particular isolation level was used for a given SQL statement:

    With Static SQL (programs with embedded SQL that get pre-compiled, compiled and the bound), the isolation level is an option on the pre-compile and bind commands.

    For Dynamic SQL, there is a default isolation level set for the database by the Create Database command. It can be changed with the AUTOCONFIGURE command (either during or after database creation). The default is RR unless it has been changed. Not sure how to see what it is (maybe a registry value). This affects ODBC and CLI interfaces to DB2.

    For a particular dynamic interface process into DB2, the isolation level can be changed dynamically with the CHANGE ISOLATION LEVEL command. This does not affect other process, but will apply to every connection made from the same command line processor back-end process.

    For JDBC and SQLJ the CLI interface default is used, but can be changed with the setTransactionIsolation method in the java.sql interface connection. In SQLJ, you run the db2profc SQLJ optimizer to create a package. The options that you can specify for this package include its isolation level.

    You can also override the isolation level with an SQL statement (at least certain statements) using the WITH clause (WITH UR, for example). This is valid on SELECT, SELECT INTO, Searched DELETE, INSERT, Searched UPDATE, and DECLARE CURSOR, SQL statements.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2004
    Posts
    18
    Thanks for your help guys. I had read your earlier post Marcus (reproduced above) - good info.

  6. #6
    Join Date
    Mar 2003
    Posts
    343
    How does one set the default isolation level on the db2 client? I tried looking under client settings but couldn't find a place for it.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2cli.ini file can be used to configure the default isolation level for a connection ...

    If none is specified, the default is CS

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2004
    Posts
    306
    If I have used the autoconfigure using isolation CS apply db and db command (is this even correct?) to set the isolation level, what other steps do I need to take?
    For example do I need to do a db2rbind all?

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I believe that the isolation level specified (you specify the isolation level typically used by your clients) in the autoconfigure command is used to configure some of the other database parameters, but does not change the default isolation level of the server.

    See the above posts to determine how to set the isolation level of your clients (or individual SQL statements).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jul 2004
    Posts
    306
    Basically I want to set the isolation level for all database operations (or as many as possible). It's the usual case of the developers think the problem is at DB end and I'm pretty sure it's at the developer end but want to be sure I've covered all possibly bases from mine.

  11. #11
    Join Date
    Feb 2002
    Location
    Philippines
    Posts
    41
    everything about the isolation level is discussed in the Administration Guide - Performance (db2d3e81.pdf) redbook... download a copy from the IBM website
    There are 10 kinds of people,
    those who know binary and those who don't.

    http://www.linkedin.com/in/rtitong

  12. #12
    Join Date
    Jul 2004
    Posts
    306
    I have read through this, I have set everything I can think of but when the application attempts an insert or update on a table it obtains a table level IX lock as well as a row lock.
    Are there Java client issues here anyone might be aware of?

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not exactly sure why you think there is a problem, but it would help to see the exact SQL statements in question and what problems you are encountering.

    The locking can depend on whether DB2 had to do a tablespace scan, or a scan of the entire index (as opposed to a b-tree scan) to find the qualifying rows, or whether an index was used with b-tree access. Any access path other than using the b-tree of the index can cause addtional locking.

    If you are still using DB2 V7, you should set the registery variable for RR_TO_RS (this is probably not the exact name of the variable to avoid next key locking problems. Type 2 indexes in Version 8 solve that problem.

    Make sure you know whether you java apps are using any of the following J2EE isolation level commands:

    Serializable - Repeatable Read (RR) in DB2

    Repeatable Reads - Read stability (RS) in DB2

    Read committed - Cursor stability (CS) in DB2

    Read uncommitted - Uncommitted read (UR) in DB2
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Mar 2003
    Posts
    60
    Hi all,

    I went thru this thread and sathya says default is CS and Marcus says its RR. I also found such conflicting documetation at IBM site and books.

    Can anyone confirm what the real default isolation level is? I have opened another thread for this discussion for JDBC specific discussion.

    thanks,
    j

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    As I mentioned in the other thread:

    "I was probably incorrect when I said awhile back that the default is RR. I believe that at one time it was the default (I have been using DB2 LUW since 1989 when it was called OS/2 Database Manager). But I think that CS is the default now. But it is confusing, espcially with the link you provided regarding the default = RR for AUTOCONFIGURE.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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