Java SQL Exception while Inserting large amount of Data

2
votes

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.

1
javadatabasejdbcdb2

1 Answers

1
votes

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