DB2 connection

[Michael Waserman]

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]

Moving this to the JDBC forum...

[David Ulicny]

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?

[Adeel Ansari]

not clear. what you really want?? could you please elaborate it more.

[Michael Waserman]

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]

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);
}