Archiving database tables using Java

Question

My application requires archiving database tables between sybase and db2 and vice versa and internally (from db2 to db2 and sybase to sybase) using java.

I am trying to understand the best strategies in terms of performance, implementation, ease of use and scalability.

Here is my current process -

• source and target tables with acceptable parameters (from java) are defined in xml. [the actual request is placed inside xml, because sometimes the parameters are accepted from java (for example, for the condition clause where))
• The application reads the source and target configurations and executes them sequentially.
• an assignment is sometimes optional when the source simply deletes data from a particular table or when the source simply calls a stored procedure.
• the data set between source and destination is extremely large (in millions)

At the top, it seems that I can determine the dependencies between several source and target combinations and execute them in parallel in several stages. But it will improve any performance (I hope so)

Are there any open source frameworks for archiving data using java? Any other thoughts on the tool side will be really helpful.

thanks

Answer 1

The most powerful open source environment for saving Java is Hibernate. You can reverse engineer the Java model from an existing database (see Hibernate Tools) and replicate using Session.replicate (). You can fine-tune performance using stateless sessions and second-level caching, where applicable. Documentation here

Answer 2

Take a look at some database replication tools (we use Shadowbase ). They may have a Java API.

Also, check out this IBM document:

[IBM] offers a solution using JDBC and the SyncML standard to achieve common database data replication.

Answer 3

Pentaho Data Integration has robust support for copying data between or from databases. In addition, it is Open Source and allows you to write plugins in Java.

Migrating from Oracle to MySQL

Answer 4

The most important thing you need to do is to disable auto-commit in JDBC, as would otherwise be the case after each insert in the database table. This disrupts performance.

But you basically have to figure out your synchronization scheme to determine which records you need to copy before you can decide how to actually do it.