Thanks for all the reply
Here is my DB2 info:
Informational tokens are "DB2 v9.1.0.4", "s071028", "U811793", and Fix Pack
"4" on Sun Solaris
I notice that I did not have the "lock" problem when I insert records but only when I update records.
First: I did not create auxiliary table and index on the aux table. This is version 9 and if I don't define the aux table it still works OK. More than that, I have only a handful of records. Please advise me if this must be done for CLOB
Second: I use Java PreparesStatment to prepare the update and close the statement after all. I even submit an extra commit to make sure that I commit.
This does not help at all.
THird: I will try to use snapshots to id the lock. I use db2pd -locks to identify locks but I don't see anything really stand out.
I run the command db2 get snapshot for locks on mydb at various stages of the process, and I can clearly see that after the update there are additional locks on the table that I update.
My question is why the lock is not released ? I set my connection to autocommit = true when I create the connection, after the update I close the PreparedStatement, and issue another commit but I still have locks.
There is some additional steps about the CLOB updates that I am missing.
Please let me know.
And I don't think that not creating the auxiliary table is creating this problem.
BTW: I use pool of connections. The Pool of Connections works fine with the previous Interbase DBMS. I know that DB2 has its own Connection Pool but I did not convert my code to use DB2 ConnectionPool yet.
Will this be the real problem ????
Thanks