Re: Export data from Mainframe to Unix solaris

From: Billy Verreynne <>
Date: Fri, 28 May 1999 08:18:04 +0200
Message-ID: <7ilcig$rc7$>

Seigmund Akinwande Johnson wrote:

> Does any one know if you can export data from mainframe to Unix or NT
> oracle export command, and what are the issues to consider

I never found a faster way to transfer data from a mainframe to UNIX than to use standard FTP ( assuming of course that the mainframe support TCP/IP and FTP) and the standard UNIX features (pipes).

This is how we did it on UNIX:

Make a FIFO pipe. Start SQL*Loader with this pipe as the input file. (I used to put a sleep in the script and then check the UNIX pid of the SQL*Loader process to make sure that it did not bomb out due to a missing control file or something).

Make another FIFO pipe. Start up UNIX dd with input file as this pipe, output file the pipe being read by SQL*Loader and conversion set from EBCDIC to ASCII.

Now fire up FTP, connect to tbe mainframe and get the data file in binary mode. Select the second FIFO pipe as the destination file (e.g. GET mainframefile pipe).

And that's it. After you have sorted out the kinks in the scripts, this is not only the fastest way to get data across, but also very robust. The kinks? - well, you need to clean up afterwards. If the FTP process falls over for some reason, an EOF will not go thru the pipes - so dd and SQL*Loader will thus not terminate. dd and SQL*Loader needs to be killed when the FTP bombs out. Ditto if SQL*Loader goes belly up due to a database problem (e.g. can not extend tablespace).

The scripting is not that complex for someone who knows a bit about UNIX sh scripting. We had this process running in a production environment, which required the usual strict error and exception handling. And we did 100's of GB of of transfers like this. I doubt that there is a faster way as the downloading of data, conversion from EBCDIC to ASCII and loading into Oracle, as all this happens in parallel with this method.

Billy Received on Fri May 28 1999 - 08:18:04 CEST

Original text of this message