Hi

Db2 Details : VersionB2 v8.1.1.96 Fix pak: 10 OS:AIX

Currently our application(Java) is using static database connections and lot of synchronize methods to do all the database operations. It works fine I mean no delays in database operations when there is not much traffic but when we have lot of traffic it slows down considerably. Reason being single database connection and synchronized blocks.

We are planning to use connection pooling and get rid of unnecessary synchronized blocks.

My concern is if we implement connection pooling there can be a situation where we can have two operations example select,update or select,delete or two updates being performed simultaneously on a table.

Will db2 take care of data integrity in cases like these or do we need to program considering all these situations

Does db2 provide for any kind of locking by default

Yes, jithudb2, DB2 (and any other multi-user database) has various locks that are taken when processing to prevent problems. Here is a link to the various Lock Attributes:

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

Thank you Stealth_DBA for your prompt reply.

In what situation/scenario can we leave it to the database to handle the data integrity issues and are there any situations where the application need to handle data integrity issues for itself or we can just leave it to the database.

jithudb2, Let me put it this way, If you can't trust the database manager to handle the basic data integrity issues you are concerned with you should get another database.

All the major databases (DB2, SQL Server, Oracle, Access, and (most likely) MySQL) handle the data integrity for you.

This is not to say you won't have problems with some transactions being locked out and abending. If there is a long running change transaction (Insert, Update and/or Delete) without commits, all other transactions trying to access the object(s) that transaction has locked can timeout. This is, after all what the database is supposed to due. (Note: there is an access that allows you to read around a lock but it must be used with care and knowledge). You will just have to make sure your table and application designs reduce the possibilities.

Not all databases use the same locking methodologies. DB2 uses pessimistic locking, which means it assumes that the programmer is not going to take of data integrity and DB2 will do it for them. In some cases, because of poorly coded applications, this may lead to lock contention (while providing data integrity), but you can work those problems out if and when they occur.

Make sure you have your LOCKTIMEOUT db parm changed from -1 to about 30 (seconds) so you will get a -911 reason code 68 if a program is waiting for more than 30 seconds for a lock to be released. Otherwise the program may wait forever for a lock to be released.

If you are having (or suspect you may have) lock contention problems, then take a look at these registry variables (db2set) to speed things up:

db2set DB2_SKIPDELETED=ON
db2set DB2_SKIPINSERTED=ON
db2set DB2_EVALUNCOMMITTED=ON (this one may require some analysis to make sure it is OK to use for a particular application).

Thank you very much for valuable advice. It does help in clearing up some doubts for me.