This week's giveaway is in the Spring forum.
We're giving away four copies of REST with Spring (video course) and have Eugen Paraschiv on-line!
See this thread for details.
The moose likes JDBC and Relational Databases and the fly likes DB2 connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "DB2 connection" Watch "DB2 connection" New topic

DB2 connection

Michael Waserman

Joined: Jul 10, 2004
Posts: 28
I have a relatively large number of records to insert into DB2 database (we are using Neon Shadow Direct) in one shot.
To save the resources and lower the responce time I open the connection, itterate through my input and close when done with DB2 calls.

The problem is DB2 table gets locked.

If I open and close connection as many times as the records I have in my input the table is fine.

Could you please help?

Thank you. Michael.
Dirk Schreckmann

Joined: Dec 10, 2001
Posts: 7023
Moving this to the JDBC forum...

[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
David Ulicny
Ranch Hand

Joined: Aug 04, 2004
Posts: 724
In general I could say where the problem is:
Commitment control - you have set autocommit true or false?
Are you using transaction explicitly?
Are you using PreparedStatement or Statement?

SCJP<br />SCWCD <br />ICSD(286)<br />MCP 70-216
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
not clear. what you really want?? could you please elaborate it more.
Michael Waserman

Joined: Jul 10, 2004
Posts: 28
I am using the cobol stored procedure to execute the transaction/s. Tried it both ways, having the explicit commit in the storproc and without it. Does not make a lot of a difference. Also tried select statement "with UR".

The most annoying thing is when one user is upploading the data (it takes 40 - 60 sec) and the other is trying to display some records both would lose the sessions.

Thanks. Michael.
Michael Waserman

Joined: Jul 10, 2004
Posts: 28
Here is the code example:
try {
pool = getPool ();
con = pool.getConnection ();
catch (Exception e) {
System.out.println (" Could not acquire a connection " + e.toString());
throw new ABCException(e.getMessage());
CallableStatement cs = null;
try {
while (iterator.hasNext()){
GetDataBean bean = (GetDataBean);
String result = "";
String functAdd = "C";
String batchNumber = bean.getBatchNum();
String tmStamp = "";
String message = bean.getMessage();
String rCode = "";

String sql = "call SYSPROC.ABCSP101(?,?,?,?,?,?)";

cs = con.prepareCall(sql);
cs.setString ( 1, functAdd);
cs.setString ( 2, batchNumber);
cs.setString ( 3, tmStamp);
cs.registerOutParameter(4, Types.CHAR);
cs.registerOutParameter(5, Types.CHAR);
cs.registerOutParameter(6, Types.CHAR);


String curmessage = cs.getString(6);
rCode = cs.getString(5).trim();
GetDataBean beanOut = new GetDataBean();

if (rCode.equals("0000")) {

pool.releaseConnection (con);
I agree. Here's the link:
subject: DB2 connection
It's not a secret anymore!