Yes! you got the points exactly!The code snippet is as below:i have put only the second part which reads DB2 andupdates oracle side.Coz this only takes much time.Hav a look @this.If we copy the same logic in a stored proc will it make faster.
String resetQuery = "SELECT t3.INSTANCE_ID FROM TOHITYP t1, TZUBZUORDNUNG t2,TZUBPREIS t3 WHERE t1.F_HIERARCHIE_ID = t2.F_BESITZER AND t2.INSTANCE_ID = t3.F_ZUORDNUNG AND t1.produktionsende = '9999-12-31' ";
Statement stmt = oracleConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet oraRs = stmt.executeQuery(resetQuery
);-->1 minute oraRs.setFetchSize(100000);
String initdeleteQuery = "UPDATE TWOIZUBPREIS SET IS_DELETED = 'N' , WHERE ZUBPREIS_ID = ? ";
long totupdatecnt = 0;boolean allrecordsover = true;
int numRecEachBatch = 100000;
//-- going for batch update
{
PreparedStatement pstmt = oracleConnection.prepareStatement(initdeleteQuery);
while(allrecordsover)
{int count=0;
while(count 0)
{pstmt.executeBatch();
pstmt.clearBatch();
}
oracleConnection.commit();
}
pstmt.close();
}--> taken 2.25 hrs.