Java SQL Exception while Inserting large amount of Data

Question

I am trying to Insert large amount of data from one table to another table. The two tables are in different regions. When I Insert data, the ID (I am using to create connection)is able to insert data for less number of rows. If It inserts data for more than 500 rows it throws exception

com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -551, SQLSTATE: 42501, SQLERRMC: DB2GCS;EXECUTE PACKAGE;NULLID.SYSLH203.

I am not able to find why it shows authorization exception for the same id if data is more.

My Code Snippet : 
 while(RSet.next()){
           stmt=test_conn.prepareStatement("Insert Query");
           for(int i=1;i<=columnCount;i++){
               stmt.setString(i,RSet.getString(i));
           }
           stmt.addBatch();;
       }
       stmt.executeBatch();

Thanks in Advance for Your Help.

Ravi K Thapliyal · Accepted Answer · 2015-02-11 18:48:04Z

2

Your code is actually not batching correctly and this could be the reason why it's breaking. The problem is that you're preparing your insert query over and over again needlessly.

You need to prepare it just once outside the loop like

test_conn.setAutoCommit(false);
stmt = test_conn.prepareStatement("INSERT INTO ...");

while(RSet.next()){
   for(int i = 1; i <= columnCount; i++){
       stmt.setString(i, RSet.getString(i));
   }
   stmt.addBatch();
}

stmt.executeBatch();
test_conn.commit();

answered Feb 11, 2015 at 18:48

Ravi K Thapliyal

51.9k9 gold badges80 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nagesh Over a year ago

Thanks a lot Ravi for your help. But after making the changes you suggested, it still takes around 3 to 4 mins to insert 400 records. can this be made faster?

Ravi K Thapliyal Over a year ago

You would need to profile your application to see where's the bottleneck; in Java, or in DB. You could use System.currentTimeMillis() just before and after the execution (and subtract the values) to find the exact time being taken by the batch out of those 3 to 4 minutes that your app is taking.

Collectives™ on Stack Overflow

Java SQL Exception while Inserting large amount of Data

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related