Loading from Oracle to Teradata 2

[JB1961]

I've been looking through past posts for information on loading Oracle to Teradata. Currently we are creating flat files, ftp'ing them to the mainframe and then using multiload to load our data. We use OLEDB for loading data from a Sybase server to Tereadata, and we have not been able to schedule these loads to take place at a given time. We must have our operators run dos batch scripts every week to load these tables. From the past posts I see that OLEDB is recommended to load from Oracle. However, the amount of data we are storing in Oracle is very large. Is there a better way than using FTP to the main frame or OLEDB to load from Oracle? Please let me know what you all are doing out there! Thanks

 

[dnoeth]

Instead of FTPing to a mainframe you could load those files directly from that server using a Unix/Windows MLoad (if your network is fast and reliable).

Regarding OLEDB it's probably less efficient than a MLoad for large data sets.

Dieter

 

[JB1961]

Our network is fast, but (sorry to sound stupid) I dont' know what I need to include to login in to Teradata from the Unix. I know on the mainframe I have my TDP ID etc. and my login script that I call from JCL. I've not used the Unix form of this. We plan to load MLoad and try it, but the logging in to Teradata is what I don't know.
Thanks....

 

[tdatgod]

The COOL thing is even on UNIX it is still called a TDPID, even though it is more correctly a COP name

define a line in your Host file that says....

192.168.1.1 tdpidcop1

192.168.1.1 is just a generic address tag. Change it to be the value for your system.

If you have more than 1 Node and you want to balance the load across all the nodes can also define....

192.168.1.20 tdpidcop2
192.168.1.30 tdpidcop3
.
.
.
192.168.1.40 tdpidcopn

up to the number of nodes your system has.


'tdpid' is the name you want to logon on to when you run Multiload or BTEQ.

.logon tdpid/user,password

The cop1 is required because the teradata tools ( via CLI ) automatically append COP1 to the name you type to verify that indeed it is a Teradata Box.

The name you use for your TDPID is just for this box. If you have mulitple Teradata boxes they all need there own TDPID names...

bobcop1
frankcop1
financecop1

Whats in a name?

The only limitation is the TDPID must be 8 charcaters or less ( 12 if include the cop1 ).


...

 

[JB1961]

Thank you SO much! I appreciate this info!

 

[san7887]

well just had a question regarding OLE LOAD.Can someone tell me how we can automate the process of loading on a weekly basis from oracle to teradata through Mload

 

[san7887]

the problem is i know how to do it in unix using kron;..........is there any way to do it in windows.....writing a script maybe to automate the oleload to get the data from the oracle and load it into the teradata....
can someone suggest anything