File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC 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 JavaScript Promises Essentials this week in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB2 connection" Watch "DB2 connection" New topic
Author

DB2 connection

Michael Waserman
Greenhorn

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
Sheriff

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
Greenhorn

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
Greenhorn

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) iterator.next();
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);

cs.execute();

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

if (rCode.equals("0000")) {
beanOut.setBatchNum(cs.getString(4);

beanOut.setMessage(message);
invalidEntries.add(beanOut);
}
}
cs.close();
pool.releaseConnection (con);
}
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: DB2 connection