|
Hi Murali,
Yes. It is a problem because it prevents executing a "file level" command like CLRPFM (that need exclusive lock), while the lock is still held by QZDASOINIT job.
Some times it is also prevents updating the file form COBOL programs.
You can see the CLRPFM problem in http://archive.midrange.com/midrange-l/200902/msg00534.html
Regards,
Tomer
-----Original Message-----
From: Murali Rao [mailto:Murali.Rao@xxxxxxxxxxx]
Sent: Tuesday, March 08, 2011 6:25 PM
To: Tomer Sason; 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)
Yes. I see Shr read lock after the second update before connection close.
I tried other ways but not able to remove it.
Is it a problem for you?
Thanks,
Murali
-----Original Message-----
From: Tomer Sason [mailto:tomer@xxxxxxxxx]
Sent: 08 March 2011 08:22
To: Murali Rao; 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)
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.
--
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 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.
midrange.com
