I am facing a strange problem with DB2
Hope to find some solution here.
I am connecting to DB2 db using com.ibm.db2.jcc.DB2Driver in my java program. In the program, I have to delete some records from a table having a blob column. Now the peculiar problem arises, executeupdate() on PreparedStatement doesnot return for the last record to be deleted although the record is deleted in the table. Hence, the application hangs and I have to forcefully terminate it. The same program works fine for table without any blob column.

Any clues or suggestions???I am really stuck with it.

Thanks in advance.

DB2 Version that we are using is DB2 v8.1.1.89 FP 9.

Did you verify that you don't run into some sort of lock timeout? You may want to set the timeout to 10 or 30 seconds or so and try again. If you get a lock timeout after the indicated time, you can try to figure out what is blocking the application. If you do not get a timeout, it may be a good idea to open a PMR with IBM support because DB2 shouldn't hang in such situations.

We managed to resolve the problem. We were selecting records from A table, inserting into B table and after that we were deleting the record from A table. I don't know whether it's mandatory to put 'with ur' with every select statement in DB2 as I am not very familiar with DB2 but after putting 'with ur' clause in the select query our problem is resolved.

Thanks for the suggestions and help.

WITH UR tells DB2 that you are willing to read uncommitted and potentially invalid data. DB2 does that by not requesting a read-lock (shared lock) on table A for the SELECT.

While the symptom goes away, I have my doubts that this is really a "solution" for your problem.

The introduction of WITH UR strongly hints at a lock-wait issue. I guess that another application also accesses tables A and/or B and does not commit, causing your application to wait for locks. Therefore, I believe that a real solution would be to check the applications that they commit frequently enough and also to configure your DB2 to have a defined lock-timeout and not the default of -1 (which means to wait forever).