JDBC DB2 and CLOB

[nghivo]

I am working on a Struts application using DB2 JDBC Type 4 Driver.
By default, the Java Connection is autocommit and everything works fine for me,
except with CLOB data type

I have a table with one CLOB column (32000 bytes). After I update the column, I issue an explicit commit just to make sure. And for some reason, a "lock" is put on that record. I can not select anything from that record any more unless
I shutdown the Struts application then I can select that record from the DB2 command line.

Does any one have any idea on this thing ? I don't think there is a lock on that record at all although everything behaves like there is a read lock on the record.

Please help.

[kalexin]

Have you create an auxiliary table and index on the aux table for your CLOB data?

[sakumar9]

You can check the lock by using snapshots. Once you have identified the lock, you can find out the reason for it.

[sakumar9]

In JDBC, you might be using prepared statements to execute the queries or to update the column. DB2 will lock the rows until you close the statement. So when you are closing your application, all the resources are freed and you are able to select the rows. Make sure that you have closed the statement.

Let me know if your problem is solved.


Regards
-- Sanjay

[nghivo]

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 PreparesStatmen t 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.

[nghivo]

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 PreparesStatmen t 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 PreparedStateme nt, 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

[sakumar9]

1. Are you using any App Server. If yes, then which??
2. I would recommend to use DB2ConnectionPo olDS class. I guess the problem is because of the class that you are using. Using DB2ConnectionPo olDS class should solve this issue. Try and let me know if you still have problems.

Regards
-- Sanjay