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.

You could change the default value in db2cli.ini:

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

Andy

Does that mean db2cli.ini need to be changed on the database server OR on the client from which application is connecting ?

It has to be on the client.

Andy

Thank you Andy.

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.

it's a BIND command option
bind ... ISOLATION [CS|RR|RS|UR] ...

For JDBC JCC Type-IV driver

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

I'm sure you've already seen this info but just in case:
IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

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.

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.

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).

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?

No, just an English teacher

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.