× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.