RE: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)

Hi Murali,
Try to use WRKOBJLCK to see the shared lock set on the file.


-----Original Message-----
From: Murali Rao [mailto:Murali.Rao@xxxxxxxxxxx]
Sent: Monday, March 07, 2011 7:16 PM
To: Java Programming on and around the IBM i
Cc: java400-l-bounces@xxxxxxxxxxxx; John Eberhard; Tomer Sason
Subject: RE: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)

There is no need to do a commit function after each SQL.

I ran the sample JAVA code given here (by Tomer) in my java test area and no shared locks put on my physical file.

I only noticed that second time if you run the statement within the same connection (CONNECTION CLOSED ONLY IF YOU CLOSE CONNECTION NOT CLOSE STATEMENT), the file is opened in that job but because I do not have shared lock, so I can exeute any number of times.
I used DSPRCDLCK inbetween selects and saw no shared locks at all.

I think it is to do with cursor usage.

I altered only the comments in the original sample code given by Tomer (and of course the ip address and user/password :):)

-------------------------------------------------------------------------------------------------------
package uat;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class TestDb2Connection {

public static final String AS400_DB2_DRIVER_NAME = "com.ibm.as400.access.AS400JDBCDriver";

public static void main(String[] args) {
String dbUser = "user";
String dbPassword = "password";
String dbURL = "jdbc:as400://dummy";

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("lazy close", "false");
prop.put("rollback cursor hold", "false");

prop.put("block criteria", 0);
prop.put("data compression", "false");
prop.put("extended dynamic", "false");
prop.put("prefetch", "false");
prop.put("hold input locators", "false");
prop.put("hold statements", "false");

try {
Class.forName(AS400_DB2_DRIVER_NAME);

// Get a connection
Connection connection = DriverManager.getConnection(dbURL, prop);
connection.setAutoCommit(true);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);

String sql = "SELECT * FROM MURALI.ACFILE1";

// Execute SQL select
Statement stmnt = connection.createStatement();
// ---> Here job QZDASOINIT was created
ResultSet rs = stmnt.executeQuery(sql);
rs.next();
// Close the ResultSet/Statement
rs.close();
// ---> File ACFILE1 closed within the job
stmnt.close();
// ---> Here no change - job still open


// ---> Now - Do it again
stmnt = connection.createStatement();
// ---> here no new job created - because we did not close the connection
rs = stmnt.executeQuery(sql);
// ---> File ACFILE1 opened within the job

rs.next();
// Close the ResultSet/Statement
rs.close();
// ---> File ACFILE1 NOT closed within the job
stmnt.close();
// ---> Here ACFILE1 still open within the job - and connection is still open - but the IO count increased


// Close the connection
connection.close();
// ---> Here job QZDASOINIT was ended


} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}


}

}

Thanks,
Murali.

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of yiyu jia
Sent: 07 March 2011 16:25
To: Java Programming on and around the IBM i
Cc: java400-l-bounces@xxxxxxxxxxxx; John Eberhard
Subject: Re: Locks are not released when executing SQL Select on AS400/DB2, using IBM Toolbox for Java (JDBC Driver)

Did you try to call commit function after each SQL executing call (even when it is only a Select statement)?

I saw a discussion about Select statement causes shared lock unreleased in PHP (on IBM i5). The conclusion is that programmer need to call commit to release the shared lock. Here is the link. the last post is the conclusion.
http://forums.zend.com/viewtopic.php?f=64&t=9237&start=10

After reading article shared by John, I feel the problem happened in PHP is also related to this pseudo closed cursor. Thanks!

Yiyu Jia


On Mon, Mar 7, 2011 at 10:31 AM, John Eberhard <jeber@xxxxxxxxxx> wrote:

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.



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

--
******************************************
* Mr. Yiyu Jia *
* *
* Email: jia.yiyu@xxxxxxxxx *
* *
* Web: http://yiyujia.blogspot.com/*
*******************************************
--
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.