File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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
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!