I am working on tool to transfer data from Oracle to Teradata and was thinking of using java-JDBC for that but when i looked over internet i could not find anything related to it but what i did find was that the data needs to be transformed before it can be moved to different RDBMS hence i just want to know if it is possible at all and if yes then some help would be appreciated.
2 Answers
Both, Oracle and Teradata databases, support JDBC.
So, if your question is if you can move data from one database to another developing your own tool, yes you can.
But, there are many third party tools that could be used to Extract Transform and Load (ETL) data from one JDBC compliant database to another.
You could start analysing the which data you have to move and making some tests using Oracle SQL Developer. It has support to connect to Teradata as well (not only Oracle). And even not being a "pure" ETL tool, I think it could be a good starting point: http://www.oracle.com/technetwork/database/migration/jdbc-migration-1923524.html
2 Comments
Vijay Shekhawat
Hi peter i have one follow up question that if i use JDBC then will i have to take care of transformation myself or will JDBC take care of it?
Peter Gibbons
If the source database structure is the same as the target database structure, there is no need for such transformation. You can use JDBC to just "copy" data between the 2 databases. The term Transformation in ETL, use to be related to aggregations and other maths applied to the data when is moved from one Online Processing database to another Analytical database, that is the most common application of ETL. Teradata is used for this purpose in most cases. If that is your case, you will need to do the Transformations... and JDBC has no "magic" for that: you have to write Java code by your own.
It depends. The Teradata JDBC driver has support for the FastLoad API which affords you some bulk loading advantages over a straight row-by-row operation. It is not as effective as using Teradata's Parallel Transporter or FastLoad utility but it won't upset your DBA's nearly as much as doing singleton inserts for large data volumes. FastLoad will support loading an empty table.
As Peter mentioned, a proper ETL process may be more appropriate especially if the data volumes are significant or this is a repeatable process. Teradata's Parallel Transporter offers you a variety of load operators depending on the data volume and target you are loading in Teradata. (i.e. low volume vs high volume, empty vs populated target table, etc.)
