I think you are seeing the effect of pseudo closed cursors. I've found
this additional information.
http://wiki.midrange.com/index.php/SQL#IBM_Support_Document_18874457
John Eberhard
Mail: jeber@xxxxxxxxxx
From: Tomer Sason <tomer@xxxxxxxxx>
To: "JAVA400-L@xxxxxxxxxxxx" <JAVA400-L@xxxxxxxxxxxx>
Date: 03/07/2011 08:51 AM
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.
midrange.com
