RE: [Maybe Spam] DB2 File in use error

Make sure your closing the prepared statement after use. The statements
given out by the jt400 driver are not designed to be reused. (Under the
covers the driver will use statement pooling).

Regards,
Chris.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Renato Katsuragawa
Sent: 24 August 2005 10:21
To: java400-l@xxxxxxxxxxxx
Subject: [Maybe Spam] DB2 File in use error

Hi,

I have the following procedure in a DB2 (AS/400 V5R1):

----------------
BEGIN 
DECLARE current_id BIGINT DEFAULT -1;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT id INTO current_id
FROM libtest.idtable
WHERE tbname = table_name;
UPDATE libtest.idtable
SET id = current_id + 1
WHERE tbname = table_name;
COMMIT WORK;
SET next_id = current_id + 1;
END 
-------------------

This procedure just return the next id (bigint) according to the table_name 
received as parameter.
Then, I developed a simple multi-threaded java application to test this 
procedure and sometimes I receive the following error:

java.sql.SQLException: [SQL0913] Row or object IDTABLE in LIBTEST type *FILE

in use.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:388)
at com.ibm.as400.access.AS400JDBCStatement.commonExecute(
AS400JDBCStatement.java:640)
at com.ibm.as400.access.AS400JDBCPreparedStatement.execute(
AS400JDBCPreparedStatement.java:692)

I have already tried changing the isolation level of the transaction but it 
didn't work correctly.
Any help?

Regards