Migrating data from teradata to Oracle

UDA
Enthusiast

Migrating data from teradata to Oracle

Hi Team,

I am new to teradata and is learning many of its features..
We want to migrate data from a teradata database to an ORacle database.
For this we opted the method of exporting TD data using fast export and import/load oracle using SqlLoader. The problem with this is there are some junk characters getting exported and is loaded to Oracle.
The export file we used is..

.LOGTABLE fexp_logtab;
.LOGON 127.0.0.1/tduser,tduser;
DATABASE dbc;
.BEGIN EXPORT SESSIONS 2;
.LAYOUT usingVars;
.FIELD VIN * char(100);
.EXPORT OUTFILE d:\fexp_expdata.txt .format text ;
SELECt ''''||trim(location_key) ||''''||','|| ''''|| trim(city)||'''' VIN from tduser.LOCATION;
.END EXPORT;
.LOGOFF;

Is there any mechanism to avoid the junk characters ?
what is the alternative to generate a .csv file from teradata ?
Also is there any better alternative methods we can adopt for the data migration from TD to Oracle.?

Plz comment.

Thanks
Shinesh
4 REPLIES
Enthusiast

Re: Migrating data from teradata to Oracle

What you can use is an ETL tool to perform your migration. There are open source tools available for free.

Talend Open Studio is an open source ETL tool for data integration and migration experts. It's easy to learn for a non-technical user. What distinguishes Talend, when it comes to business users, is the tMap component. It allows the user to get a graphical and functional view of integration processes. Talend also has connectors to both databases you are needing to migrate data.

For more information: http://www.talend.com/
Enthusiast

Re: Migrating data from teradata to Oracle

Hi,

Why don't you try exporting data using Teradata SQL Assistant and see whether you still have Junk characters in the exported file.
Enthusiast

Re: Migrating data from teradata to Oracle

Hi,

I m thinking this way:
.LOGTABLE fexp_logtab;
.LOGON 127.0.0.1/tduser,tduser;
DATABASE dbc;
.BEGIN EXPORT SESSIONS 20;
.EXPORT OUTFILE 'your path' FORMAT TEXT;

SELECT (CASE WHEN location_key IS NULL THEN CAST('?' AS CHAR(1)) ELSE TRIM(CAST(location_key as char(20))) (TITLE ''),
CAST('-' AS CHAR(1)),
(CASE WHEN CITY IS NULL THEN CAST('?' AS CHAR(1)) ELSE TRIM(CAST(CITY AS CHAR(20))) (TITLE '')
FROM tduser.LOCATION;

.END EXPORT;
.LOG OFF;

Subsequently, we can remove the junk charcaters. In unix it is easy.

Rgds,
Raja
Enthusiast

Re: Migrating data from teradata to Oracle

hii,

Thanks much for the suggestions..
I did resolve the issue using cast.
Now trying the possibility of the tool you have mentioned.

Thanks
Shinesh