Hi All

This command is used in MS SQL/MySQL
ALTER DATABASE <Database Name> SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE GO

What is the equivelant for DB2?

Maybe you should enlighten us as to what it is doing. Also supply your DB2 version and OS.

Andy

Also explain what exactly a "database" is in terms of MS SQL Server or MySQL (whichever you are really referring to). That term is by no means clearly defined and different database systems denote different things with it.

I'm refering to Ms SQL and MySQL because the same command is used in both.
The command is the same as what you would run "NOLOCK" on each table but in this case it runs on the whole database.
I'm running DB2 V9.1 FP4 on AIX 5.3

Apparently this is like accessing the database via Uncommitted Read isolation level, where no locks are taken. There is no way that I know of to do this at the database or table level in DB2. One can set the default isolation level to UR for the client, or for statically bound packages, or in an individual SQL by specifying "WITH UR" at the end of the statement (this last method is usually the most popular).

There are also some environment variables to improve concurrency that can be turned on with the db2set command at the instance level (not at database level):

db2set DB2_SKIPINSERTED=ON
db2set DB2_SKIPDELETED=ON
db2set DB2_EVALUNCOMMITTED=ON

In version 9.7 there is a new parameter that is the default for newly created databases call cur_commit which will allow DB2 to select the previous state of a any locked data from the transaction log instead of waiting for a lock to be released.

Thank you MarcusA, this is what I'm looking for. I want to set the default issolation level to UR where every connection that is made will automatically use this level. What command do I use for this?

I told you in my response that there is no command like that at the database level. You can set that at the client level. The command for changing the client depends on what client you have.