How to fix JdbcOperations Batch Update consuming a lot of time?

455 Views Asked by At

I am trying to update million rows of DB2 through JDBC operations batch update but I am not able to. After certain time I am getting exception

DB2 SQL Error: SQLCODE=-904, SQLSTATE=57011, SQLERRMC=00C90096;(Resource unavailable)

This happens when many records get updated without commit, in my case I am getting this exception after updating 10k rows. I can not repeatedly commit after some update queries, I want to update all or not at all. I am using below code of batch update and created batch size of 10000.

Library: org.springframework.jdbc.core.JdbcOperations

Code

String query="Update Table SET Column1 = ?, Column2 = 'Data1', LST_TRNS = CURRENT TIMESTAMP WHERE Column2 = ? AND Column3 = ? ";
            
            int arr[][]=    jdbcOperations.batchUpdate(query, rows, 10000, 
                    new ParameterizedPreparedStatementSetter<Map<String, Object>>() {
            
                @Override
                public void setValues(PreparedStatement ps, Map<String, Object> rows) throws SQLException {
                    ps.setInt(1,value1); //coming from method parameter
                    ps.setString(2, rows.get("value2").toString());
                    ps.setInt(3,(Integer)rows.get("value3"));
                    
                }
                    }
                    
                    );

How can I modify the code or use something else to achieve my goal?

0

There are 0 best solutions below