0

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?

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Ayush
  • 206
  • 7
  • 22
  • The code 00C90096 means you are running out of row-locks. Speak with your DBA and ask about increasing NUMLKUS, and to verify that the access-plan for your query is index-only access. Follow the guidance (programmer response) in the documentation https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/codes/src/tpc/00c90096.html – mao Sep 25 '20 at 16:27
  • I talked to them, DB2 is returning that exception after modification of 10k rows and unfortunately that can not be modified. – Ayush Sep 25 '20 at 17:25
  • Then you must adjust your design. – mao Sep 25 '20 at 17:27
  • Can you suggest something? – Ayush Sep 25 '20 at 17:28
  • Have you tried using a smaller batch size than 10000. Larger batches are not always better. – Mark Rotteveel Sep 25 '20 at 18:09
  • Yes, I tried with 1000 and got the same issue. – Ayush Sep 25 '20 at 18:10
  • 1
    Only a smaller unit-of-work (commit more often) will release the locks. Analyze your requirement for "all or nothing " more carefully to determine if it is a real or false requirement. A meaningful conversation between experienced developers and DBAs should yield the best technical approach, which may involve using a stored-procedure, special utility job, and may depend on the physical table design. Get some experienced consultants to advise. – mao Sep 25 '20 at 20:14

0 Answers0