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

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

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.

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.

Thanks for your help guys. I had read your earlier post Marcus (reproduced above) - good info.

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.

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

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?

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

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.

everything about the isolation level is discussed in the Administration Guide - Performance (db2d3e81.pdf) redbook... download a copy from the IBM website

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?

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

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

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.