Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / April 2006

Tip: Looking for answers? Try searching our database.

DB2 performance variables

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
leonardobgil@gmail.com - 21 Apr 2006 22:22 GMT
Does anyone know of any problems on setting the following db2
environment variables?

DB2_EVALUNCOMMITTED=ON
DB2_SKIPINSERTED=ON
DB2_SKIPDELETED=ON

Also is it correct to assume the effect of those settings would be the
same as running a query "WITH UR" ?

Thank you

Leo
Mark A - 22 Apr 2006 00:40 GMT
> Does anyone know of any problems on setting the following db2
> environment variables?
[quoted text clipped - 9 lines]
>
> Leo

There are some circumstances when an application is coded in a certain way,
where you might not want to use the settings. For more information on when
this might be, see this link:
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0509schuetz/

These are not really performance settings, but they can minimize lock
contention, so if you have a lot of lock wait time in your application you
may be able to increase performance. Also, these settings can "sometimes" be
useful in preventing deadlocks when application programs are not coded in
the proper way that would avoid them.

Using these settings is not the same as running a query "WITH UR" although
in some very specific circumstances with specific queries, the result may be
the similar in that lock wait time is eliminated..

Note that the above settings are ignored on queries which use the RR
isolation level.
Serge Rielau - 22 Apr 2006 01:51 GMT
> Does anyone know of any problems on setting the following db2
> environment variables?
[quoted text clipped - 5 lines]
> Also is it correct to assume the effect of those settings would be the
> same as running a query "WITH UR" ?
That is incorrect. WITH UR will see uncommitted rows. The variables
above skip uncommitted rows.

DB2_SKIPINSERTED is safe (there is nothing wrong about not seeing a new
row that isn't committed yet).

The other two may or may not be safe depending on your app.

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.