Slow performance in Insert records through JDBC, using
jt400.jar

Hi All<br />
<br />
Trying to insert say 10000 records to a table in DB2 in iSeries V5R4 from a Java
Program using JDBC connection and jt400.jar. The insert is taking close to 18
Mins even while using the batch insert. Need some help on any connection or
other parameters that need to be set.<br />
<br />
I understand that the addBatch() and executeBatch() should perform atleast 10
times faster than the executeUpdate(). Tried different options and the data is
as provided below.<br />
<br />
1. adding 10,000 records using addBatch() and then used executeBatch() this took
18.58 Mins.<br />
2. Programatically handled: once 50  records added to batch call executebatch()
and then clearBatch and then add again. This took 18.25 Mins<br />
3. When creating connection set the autocommit to false and then did a commit
after executeBatch(). This took 17.45 Mins<br />
4. Replaced addBatch() and used executeUpdate() method to insert records in the
loop. This took 17.46 Mins.<br />
<br />
Note that there is no difference between executeUpdate() and using batch. How
can I make the batch insert more faster. Any help on this is really
appreciated.<br />
<br />
Additional Info:<br />
1. No Indexes on tables<br />
2. The table has a timestamp field and the query has CURRENT_TIMESTAMP as value
in the field.<br />
3. The time duration for insert is calculated as Max(timestamp) - Min(timestamp)
in the table.<br />
<br />
Thanks<br />

My first thought is that they may not be using PreparedStatements...<br
/>
If they are using regular AS400JDBCStatements instead of
AS400JDBCPreparedStatements, then they will experience a performance
hit...<br />
-With Toolbox  AS400JDBCPreparedStatement, we concatinate all of the data into a
single IO.<br />
-With Toolbox AS400JDBCStatement, we do NOT concatinate all of the data into one
IO; we do a separate IO for each insert.<br />
<br />
-Note that the i5 Native JDBC driver has a flag for this:<br />
<br />

I use import java.sql.PreparedStatement<br />
<br />

How much data has to be transferred for a record, i.o.w. do you have one 
or more large note fields? Have you made sure you're not running into a 
network/bandwidth limitation?