Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557

    Unanswered: Changing Isolation level

    For what ever reason DB was created with a default, CS, isolation level. this is a warehouse db and there was no reason for this default. I see applications bumping into each other for no reason other then this setting.

    I have never had to reset this parm for a whole db and looking now at the possibility of changing it. From everything that I have found it appears that I might be SOL as I can not find a way to permanently setting this level to UR as a new default.

    I am not going to be able to convince app people to set it correctly on the fly. So, it has to be done at the DB level.

    Is there a way to reset Isolation level with out a need of rebuilding db?

    Thank you.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You could change the default value in db2cli.ini:

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    Andy

  3. #3
    Join Date
    Jun 2009
    Posts
    272
    Does that mean db2cli.ini need to be changed on the database server OR on the client from which application is connecting ?

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It has to be on the client.

    Andy

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Thank you Andy.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    A database cannot be created with any isolation level, and it cannot be set at the database level, because it is an attribute of a connection.

    Also, I suspect db2cli.ini may have no effect on JDBC connections, if that's what your application uses.
    Last edited by n_i; 04-14-10 at 16:06.

  7. #7
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    it's a BIND command option
    bind ... ISOLATION [CS|RR|RS|UR] ...
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  8. #8
    Join Date
    Jun 2009
    Posts
    272
    For JDBC JCC Type-IV driver

    You can set setTransactionIsolation method in the java.sql interface connection.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I'm sure you've already seen this info but just in case:
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

  10. #10
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by n_i View Post
    A database cannot be created with any isolation level, and it cannot be set at the database level, because it is an attribute of a connection.

    Also, I suspect db2cli.ini may have no effect on JDBC connections, if that's what your application uses.
    Nick,

    AUTOCONFIGURE section of the db create allows to change the default value.

    Bella, talk to someone. There got to be an easier way to change this setting globally.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Alex, What you're trying to do (based on my understanding - set isolation level for all connections at the db level / db creation time) cannot be done.

    autoconfigure calculates some config parameters based on info (including isolation level) you supply, but it doesn't set/change the isolation level for the database.

  12. #12
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by db2girl View Post
    Alex, What you're trying to do (based on my understanding - set isolation level for all connections at the db level / db creation time) cannot be done.

    autoconfigure calculates some config parameters based on info (including isolation level) you supply, but it doesn't set/change the isolation level for the database.
    bella, I might be misreading the manual, but in Command Reference ver 9 page 402 states the following:"Isolation level of applications connecting to this database"

    This tells me that if I provide this during the build all connection will be set at that level by default.

    In the warehousing env where 99.9% of all activity is a SELECT queries and almost non existent daily updates I see no reason for CS isolation level.

    Expecting developers to Code "with UR" or other means of resetting it, is like expecting to win a lottery. So, there would have been nice to have a parm where it can be set globally.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Cougar8000 View Post
    Command Reference ver 9 page 402 states the following:"Isolation level of applications connecting to this database"

    This tells me that if I provide this during the build all connection will be set at that level by default.
    I think what it means is: "I'm expecting that my applications will set their isolation level to _whatever_ when connecting to the database, so please configure the database taking that into consideration".

    In 9.7 you can set the cur_commit database configuration parameter to ON to modify behaviour of applications using the CS isolation level (which remains the default).

  14. #14
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Nick,

    Is there a lawyer in the house to decipher what that means

    So when we finally make to 9.7 cur_commit will get set to DISABLED making UR a default, correct?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Cougar8000 View Post

    Is there a lawyer in the house to decipher what that means
    No, just an English teacher

    Quote Originally Posted by Cougar8000 View Post
    So when we finally make to 9.7 cur_commit will get set to DISABLED making UR a default, correct?
    If cur_commit is DISABLED, the behaviour of applications using "cursor stability" will revert to the original. It will still be the default isolation 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
  •