• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Design help for the Data Loaders

 
Purvesh Vora
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

We are planning to write an application which will read around one million records from source database. Then it will join with the existing tables in the target schema and update/insert records in the target schema. The target schema is in Sybase database.

At higher level there could be two possible ways to do it
1. Do all these processing in shell scripts/stored prcedures.
2. Use JDBC/any relational database. And do the processing in Java.

From the monitoring/code maintenance perpspective we would prefer second option.

Also planning to use J2SE.

Can you please tell pros/cons of using plain JDBC/Hibernate/any other related technologies & any suggestions.

Thanks in advance.

Best Regards,
Purvesh
[ April 24, 2008: Message edited by: Purvesh Vora ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you are moving large datasets, are you doing any transformations like you have to change some of the data before you move it to the new database.

Anyway, when I read a post like yours, I always have to say, use the right tool for the job, not a tool because you like it.

For such mass moving, I wouldn't recommend Java at all. I would use the tools that are specifically written for such jobs. ETL tools. And that includes some scripting. For instance sqlldr in Oracle.

Mark
 
Cameron Wallace McKenzie
author and cow tipper
Saloon Keeper
Posts: 4968
1
Hibernate Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mark is right.

I'm on a project right now that uses Hibernate for the application, but large deltas of data changes are moved nightly. Those large data moves never see anything to do with Hibernate, and instead, are moved using DataStage, IBM's ETL tool.

-Cameron McKenzie
 
Purvesh Vora
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Mark & Cameron.

I need to modify the data(get reference data from different tables) before inserting into the database. In one of my earlier project I had used SQL Loader and stored procedures(with oracle db) to do the same. And its working perfectly.

In the current project we are using sybase & the current one is written in java. But the issue over here is that monitoring the job progress & managing it(restarting/failover)is pretty much difficult. Also the current desing is not perfect & gives uneven performance results. However the database also needs to be tuned in this case.

Best Regards,
Purvesh
[ April 25, 2008: Message edited by: Purvesh Vora ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic