Hi Murali,
Thanks for your reply.
For Test purpose, I used only SQL select statement (i.e, executeQuery method).
I try to use also PreparedStatement, but still the same problem occurs.
Regards,
Tomer
-----Original Message-----
From: Murali Rao [mailto:Murali.Rao@xxxxxxxxxxx]
Sent: Monday, March 07, 2011 5:26 PM
To: Tomer Sason
Cc: Java Programming on and around the IBM i
Subject: FW: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)
Hi Tomer
Please see my original reply to your question below.
Thanks,
Murali.
-----Original Message-----
From: Murali Rao
Sent: 07 March 2011 15:23
To: 'Java Programming on and around the IBM i'
Subject: RE: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)
I'm surprised to see select statement puts shared lock on db2 file.
We used extensively sql and stored procedures from java to iseries, and had no locking issues.
You may have to review your java code.
Your statement can be prepared statement here as explained below Try using only execute here - rs = stmnt.executeQuery(sql);
We use the following
connection = DriverManager.getConnection(Configuration.Getconfiguration().geti5UDBURL(),
Configuration.Getconfiguration().geti5UDBUserID(),
Configuration.Getconfiguration().geti5UDBPassword());
String insertStatement = " ";
insertStatement = "INSERT INTO " + Configuration.Getconfiguration().getI5UDBPolicyEndorsementLibraryFileName()+ " ("etc etc etc."
PreparedStatement statement = connection.prepareStatement(insertStatement);
Then few statement.setString etc.
//execute the preparedstatement insert
int DBUpdateResult = statement.executeUpdate();
statement.close();
if (DBUpdateResult > 0) {
return true;
} else {
return false;
}
}
-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Tomer Sason
Sent: 07 March 2011 14:46
To: JAVA400-L@xxxxxxxxxxxx
Subject: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)
Hello,
I am a java programmer and I am using AS400/DB2 database.
In order to connect to the database I am using IBM Toolbox for Java (JTOpen version 6 and I try also version 7.3).
It seems that executing the same SQL select more than once, with the same connection, keeps locks on records from the result set, even if the Statement or ResultSet objects are closed.
The problem occurs both with connection that was created by DriverManager and connection that was taken form Connection pool.
Description:
When using IBM Toolbox for Java to execute SQL select statements, a job named QZDASOINIT is created with a shared lock on the record/file.
When the Statement (that executes the SQL select) is closed (or the ResultSet object is closed) the job QZDASOINIT should be ended.
A test shows that when we executes SQL select once and then close the Statement object (or the ResultSet object), it cause the job QZDASOINIT to be ended.
When we try to execute the exactly same SQL select with the same connection, then close the Statement object, the job QZDASOINIT is remaining and the shared lock is also remaining.
When we close the connection the all the jobs (QZDASOINIT) that were created using this connection are ended and the locks are free.
For instance, if we execute 2 different SQL select with the same connection, say A and B
1. execute the A sql select (cause QZDASOINIT job to be created with shared lock)
2. closing the Statement object (cause to end the job and release the lock)
3. execute the B sql select (cause QZDASOINIT job to be created with shared lock)
4. closing the Statement object (cause to end the job and release the lock)
5. execute the A sql select again (cause QZDASOINIT job to be created with shared lock)
6. closing the Statement object (The job is NOT ended and the lock is still exists)
7. execute the B sql select again (cause QZDASOINIT job to be created with shared lock)
8. closing the Statement object (The job is NOT ended and the lock is still exists)
Now 2 locks are exists
For each SQL Select the executes more than once with the same connection, the job QZDASOINIT) and the lock are not released.
The parameters that I try to use with the connection are:
Key
Value
xa loosely coupled support
0
transaction isolation
read uncommited
cursor hold
false
rollback cursor hold
false
cursor sensitivity
asensitive
lazy close
False
block criteria
0
data compression
false
extended dynamic
false
prefetch
false
hold input locators
false
hold statements
false
trace
true
// The code for getting the connection:
Connection connection = null;
// Initiate connection properties
java.util.Properties prop = new java.util.Properties(); prop.put("user", dbUser); prop.put("password", dbPassword); prop.put("xa loosely coupled support", "0"); prop.put("transaction isolation", "read uncommited"); prop.put("cursor hold", "false"); prop.put("rollback cursor hold", "false"); prop.put("hold input locators", "false"); prop.put("hold statements", "false"); . . .
prop.put("trace", "true");
connection = DriverManager.getConnection(dbURL, prop); connection.setAutoCommit(true); connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED)connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
// The code to exeute SQL Statment:
String sql = "SELECT * FROM MYLIB.MYFILE"; Statement stmnt = connection.createStatement(); ResultSet rs = stmnt.executeQuery(sql); // ---- Now the job QZDASOINIT is create with shared lock
. . .
rs.next();
stmnt.close();
// ---- Now the job QZDASOINIT is ended
stmnt = connection.createStatement();
rs = stmnt.executeQuery(sql);
// ---- Now the job QZDASOINIT is create again with shared lock
. . .
rs.next();
stmnt.close();
// ---- Now the job QZDASOINIT is still remaining with shared lock
Thanks,
Tomer S.
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list To post a message email: JAVA400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/java400-l.
Consider the environment and think before you print this email.
Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA Registered Number: 354568 England
Authorised and regulated by the Financial Services Authority
This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.
Consider the environment and think before you print this email.
midrange.com
