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



Hi Robert,
Thanks. This is a good solution but in our case it is not sufficient one because the CLRPFM is used by many CL programs and moreover I believe that there are more commands that need exclusive lock.
I prefer to make the required changes in my Java programs rather than in the CL programs or alternatively to set properties for the AS400/DB2 in the configuration file that impact both Java programs and CL programs.

Regards,
Tomer


-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx [mailto:java400-l-bounces@xxxxxxxxxxxx] On Behalf Of Robert Arce
Sent: Wednesday, March 09, 2011 5:16 PM
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)

Tomer, one thing you could to get around this in case you cannot find the
actual fix is to replace the CLRPFM by SQL statement that deletes every
record of the table.

Hope it helps...
Robert Arce

--------------------------------------------------
From: "Tomer Sason" <tomer@xxxxxxxxx>
Sent: Wednesday, March 09, 2011 12:53 AM
To: "Murali Rao" <Murali.Rao@xxxxxxxxxxx>; "Java Programming on and around
theIBM i" <java400-l@xxxxxxxxxxxx>
Cc: <java400-l-bounces@xxxxxxxxxxxx>; <John@xxxxxxxxxxxxxxxxxxxx>;
"Eberhard" <jeber@xxxxxxxxxx>
Subject: RE: Locks are not released when executing SQL Select on
AS400/DB2,using IBM Toolbox for Java (JDBC Driver)

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.


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

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.