• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

Performance problem in jdbc

 
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Dear all,
I need your valuable ideas on improving below logic on replicating data fromDB2 to Oracle 9i.We have a huge tables in DB2 to replicate to Oracle side.
For one table this taking lot of time.The whole app' is written in java.
The current logic is Setting soft delete to specific set of records in oracle table and Reading all records from DB2 table to set only these records in oracle table to 'N' so that deleted records got soft deleted in oralce side.The DB2 query is having 3 table join and taking nearly 1minute using this resultset We are updating the oracle table in batch of 100000.For 610275 records update in batch mode is taking 2.25 hours .Combinely ,the first update to all Y' and second update to N' using DB2 query is taking 2.85 hrs.this has to be reduced to <=1hour.The same approach in other table is taking only 1.15 hrs as a whole, but here the corres.DB2 query has only 2 tables join.The 3table join could be the problem in this problematic case??
Do you have any clever idea to reduce this time?? kindly help us.Even new approach in above logic to replicate also welcome..
 
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your question is not clear and a bit confusing. Please make shorter sentences.

Is this just migrating from one DB2 table to Oracle DB or is there sync also involved?
 
Muthu Ponmozhi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello,
I am sorry for my large post. here is the short form. We have a java application to replicate data from db2 to oracle db (running weekly).As part of this,we have delete mode run which is making the 2 dbs in sync for the deleted records in db2 but not in oracle.This is taking lot of time for one particular huge table.The logic is like setting all records in oralce table to Y then read ids from db2, to set updated to not deleted in oracle. This is done using batch concept in jdbc executeBatch().DB2 query is taking 1 minute to read records with where clause of 3 tables join and updating in oracle table is involving 6 batches of 6.10 lak records taking 2.25hrs. we would like to reduce this time. I hope I'm little bit clear now.
May be using the resultset directly with this huge table query to update our db is taking time?? Any suggestions welcome.
 
Rajah Nagur
Ranch Hand
Posts: 239
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Muthu Ponmozhi:
Hello,
I am sorry for my large post. here is the short form. We have a java application to replicate data from db2 to oracle db (running weekly).As part of this,we have delete mode run which is making the 2 dbs in sync for the deleted records in db2 but not in oracle.This is taking lot of time for one particular huge table.The logic is like setting all records in oralce table to Y then read ids from db2, to set updated to not deleted in oracle. This is done using batch concept in jdbc executeBatch().DB2 query is taking 1 minute to read records with where clause of 3 tables join and updating in oracle table is involving 6 batches of 6.10 lak records taking 2.25hrs. we would like to reduce this time. I hope I'm little bit clear now.
May be using the resultset directly with this huge table query to update our db is taking time?? Any suggestions welcome.




This is what I understand.

You have a batch job that runs weekly which syncs the deleted records from DB2 to Oracle.
The number of records in the Oracle Table is huge ( 6 lakh records).
The number of records in DB2 is relatively less.

The sync is done as follows:
1. Update all the records in the Oracle Table to "Y".
2. Select all the records from the DB2 that needs to be synced (i.e. deleted in Oracle Table)
3. Run a batchUpdate on Oracle to update the records selected from above DB2 query to set the flag to "N".

Is this correct?

Have you considered using Stored Procs.??

Send the DB2 results to a stored proc in Oracle to sync.

If possible paste the sample code.
 
Muthu Ponmozhi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Muthu Ponmozhi
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,
We got performance improvement! Let me explain how we got it! We just created indexes in the DB2 side for those columns used in the select query and hence we got it by 1.30 hrs which is okie for our problem.
Thanks to Raja and all who tried a lot for my problem.
 
My, my, aren't you a big fella. Here, have a tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic