Archiving Database Tables using Java

Question

My application demands archiving database tables between sybase and db2 and vice-a-versa and within(db2 to db2 and sybase to sybase) using java.

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

Here is my current process -

• source and destination tables with the acceptable parameters (from java) are defined within xml. [actual query is being placed inside the xml because at sometimes parameters are accepted from java (for a where clause condition for example)]
• the application reads the source and destination configurations and execute them sequentially.
• destination is sometimes optional when source is just deleting data from a specific table or when the source is just calling a stored procedure.
• dataset between source and destination is extremely large (in millions)

From top of my head, it looks like I can define dependencies between multiple source and destination combination and have them execute in parallel in multiple treads. But will this improve any performance(i hope it will)?

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

Thanks

Answer 1

The most powerful open source framework for Java persistence is Hibernate. You can reverse engineer Java model from existing DB (see Hibernate Tools), and perform a replication using Session.replicate(). You can fine tune performance by using stateless sessions and second level caching where applicable. Documentation is here

Answer 2

Look at some database replication tools (we use Shadowbase). They might have Java API's.

Also, check out this IBM whitepaper:

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