Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2002
    Posts
    25

    Unanswered: setting the transaction isolation level

    Oracle and SQL Server provide
    SET TRANSACTION ISOLATION LEVEL
    stm to set the isolation level of a transaction.

    Is there anything similar that works in DB2?
    What is the default isolation level that DB2 uses?

  2. #2
    Join Date
    May 2002
    Posts
    30

    Re: setting the transaction isolation level

    I have read your posts in this forum and appears to me that you are new to DB2 though you are an experienced database programmer.

    Can I suggest you a few resources to help you become competent in DB2 :

    DB2 Fundamentaks - Certification Tutorial

    http://www7b.boulder.ibm.com/dmdd/li...2cert_tut.html

    DB2 Programming FastPath Course (for experienced database developers)

    http://www-3.ibm.com/software/data/db2/ct10crs/


    Apart from the above tutorials :

    Appl Development using DB2 :

    http://www-3.ibm.com/software/data/db2/udb/ad/

    DB2 Developer Domain:

    http://www7b.boulder.ibm.com/dmdd

    Regards

    Soundh

    Originally posted by vdesai
    Oracle and SQL Server provide
    SET TRANSACTION ISOLATION LEVEL
    stm to set the isolation level of a transaction.

    Is there anything similar that works in DB2?
    What is the default isolation level that DB2 uses?

  3. #3
    Join Date
    Apr 2002
    Location
    TX
    Posts
    13

    Isolation Levels in db2

    Yes, Isolations are available in db2 also. They are UnCommitted Read, Cursor Stability( default), Read Stability, and Repeatable read.

    And You can change this levels by using

    db2 CHANGE SQLISL TO {CS | RR | UR | RS | NC}

    Hope this will help some way.


    Hari Gosangi.

  4. #4
    Join Date
    Jul 2002
    Posts
    25
    How do u run this command from a stored procedure

    db2 change sqlisl cs

  5. #5
    Join Date
    Aug 2002
    Posts
    12
    Hi,

    This is a db2 command, so you cannot use it in your program.
    But There's a many way that you can program ISOLATION .

    - Static SQL application
    - you can specify isolation level at BIND command
    - same when you you SQLJ

    - Dynamic SQL application
    - Java use java.sql.setTransactionIsolation method
    - DB2 CLI
    ...etc

    Please refer to "DB2 Administration " manual . Chapter 22.

    also, please notice that Isolation and Lock is a different thing.
    you can control Lock mode at Statement level from application,
    but you cannot control Isolation at Statement level.
    because it is applied for application( transaction ) itself.
    (Oracle can use " set transaction " inside PL/SQL )

    Regards.

  6. #6
    Join Date
    Jan 2003
    Posts
    2

    Setting the transaction isolation level

    It is possible to set the transaction isolation level for many different SQL statements.

    With a SELECT statement, you can add to the end:
    WITH RR | RS | CS | UR

    Where RR, RS, CS, and UR are the isolation levels repeatable read, read stability, cursor stability, and uncommited read.

    For example, to perform dirty reads:
    SELECT * FROM mytable WITH UR

    This function works in DB2 7.2, but wasn't very well documented. The documentation for DB2 8.1 is much better and this option is included.

  7. #7
    Join Date
    Jun 2003
    Location
    INDIA
    Posts
    3

    Re: Isolation Levels in db2

    Hi Hari,

    Is it possible to use "set transaction use rollback segment rb1' in db2?

    Plz mail me asap to suryaprakash@angelfire.com

    B.Suryaprakash


    Originally posted by hari gosangi
    Yes, Isolations are available in db2 also. They are UnCommitted Read, Cursor Stability( default), Read Stability, and Repeatable read.

    And You can change this levels by using

    db2 CHANGE SQLISL TO {CS | RR | UR | RS | NC}

    Hope this will help some way.


    Hari Gosangi.

  8. #8
    Join Date
    May 2003
    Location
    Phoenix, US
    Posts
    39

    Re: Isolation Levels in db2

    Hi,
    There is no concept of rollback segments in DB2UDB. Everything is wrtten to trnasaction logs.

    I do not think of any option where you can force the DB2 UDB to use a particular log file...

    Thanks
    Sateesh
    Originally posted by suryaprakash_b
    Hi Hari,

    Is it possible to use "set transaction use rollback segment rb1' in db2?

    Plz mail me asap to suryaprakash@angelfire.com

    B.Suryaprakash

Posting Permissions

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