Re: IBM Mainframe to midrange data transfers

From: Kirk Bradley <kbradley_at_us.oracle.com>
Date: 1997/02/28
Message-ID: <kbradley-ya023180002802971044220001@192.86.155.83>

Absolutely incorrect Zoran. SQL*Loader, on a per field basis, can specify the 'from' character set. You can therefore say that a field is EBCDIC and the right thing will happen when it's loaded on any platform including plain old ASCII. It's also true that SQL*Loader can read packed decimal just fine. So yes, go ahead and transfer your mainframe file in binary and just modify the SQL*Loader control file to tell it which fields are EBCDIC and you should be all done....

In article <3310D10A.1088_at_echo-on.net>, zoranm_at_echo-on.net wrote:

> ------------55993A0E45082
> Content-Transfer-Encoding: 7bit
> Content-Type: text/plain; charset=us-ascii
>
> Peter Cumming wrote:
> >
> > We usually transfer files from our Mainframe (IBM) to a midrange like
> > RS6000 running Oracle. We first unpack the fields that may be COMP or
> > COMP-3 from the mainframe.
> >
> > We then transfer the data from the mainframe (EBCDIC to ASCII) to the
> > midrange and import the data using SQLLOADER (I think) into Oracle.
> >
> > I was told that since the Midrange Oracle SQLLOADER can handle binary
or
> > packed data types that we do not need to write the COBOL program on
the
> > mainframe (IBM) to unpack data....
> > Is this true or false?????
> >
> > Then someone brought up the fact that we would need some tool that
would
> > transfer the data from our mianframe to the midrange in a binary
fashion
> > since the normal transfer that occurs is more of a text based type of
> > transfer and could not handle the transfer of a packed field...
> >
> > Any input on that?
> >
> > Our user is convinced this can be done somehow by buying some binary
> > transfer (whatever that woule be) program and letting Oracle SQLLOADER
> > do the unpack.
> >
> > Ouur contention is that you cnanot do this unless you spend big money
> > for a transofrmation program like ETI EXtract, Carleton or Prism or
IBI
> > to do this for you. Same as you would manually but it automates it.
ie:
> > no binary transfer.
> >
> > Appreciate anyone who can put this to rest.
> >
> The most basic problem here is the different character sets used on the
> different computers. Your mainframe uses EBCDIC and the RS6K uses ASCII.
> If SQLLoader (on RS6K) could read in EBCDIC data, then you might be able
> to get away without conversions. This however is not the case.
>
> So, you are correct in what you are doing... unpacking the COMP and
> COMP-3 to just plain text then downloading and having the download
> software do the ASCII->EBCDIC translation.
>
> There are gateway type products; Oracle even has one (Oracle Gateway),
> but I hear it is expensive. Depending on your needs, your cobol pgms may
> do just fine.
>
> You may use your cobol pgm to sort/summarize data on the mainframe (if
> required) so that the number of rows being downloaded is less. You can
> also get around the necessity of the dot (".") for dollar amounts - I
> believe you can tell SQLLoader that 2 decimals are to be implied.
>
> If you have TCP/IP for the mainframe, you can make use of the FTP pgm in
> batch to automate the download to the RS6k. Watchout for ftp errors
> though. If you don't specify PARM=16 in the EXEC line then any ftp
> errors will still yield a zero RC for the step. The FTP pgm will take
> care of EBCDIC to ASCII translations.
>
> If your using fixed format files and are experiencing very large flat
> data files, give thought to making the flat files variable length (comma
> delimited). This needs some tricky COBOL code, but can be done; and can
> save lots of space, depending on the data.
>
> Another possibility is the use of sockets to program a pro-c pgm on
> RS6K, and a cobol pgm with CICS-sockets or MVS-sockets to transmit your
> data across a socket. This would be more suitable for real time
> situations... much is possible, depending on your needs.
>
> Hope this helps.
>
> Zoran.
>
> ------------55993A0E45082
> Content-Transfer-Encoding: 7bit
> Content-Type: text/html; charset=us-ascii
>
> <HTML><BODY>
>
> <DT>Peter Cumming wrote: 
> >  
> > We usually transfer files from our Mainframe (IBM) to a midrange like 
> > RS6000 running Oracle. We first unpack the fields that may be COMP
> or 
> > COMP-3 from the mainframe. 
> >  
> > We then transfer the data from the mainframe (EBCDIC to ASCII) to
> the 
> > midrange and import the data using SQLLOADER (I think) into Oracle. 
> >  
> > I was told that since the Midrange Oracle SQLLOADER can handle binary
> or 
> > packed data types that we do not need to write the COBOL program on
> the 
> > mainframe (IBM) to unpack data.... 
> > Is this true or false????? 
> >  
> > Then someone brought up the fact that we would need some tool that
> would 
> > transfer the data from our mianframe to the midrange in a binary
fashion 
> > since the normal transfer that occurs is more of a text based type
> of 
> > transfer and could not handle the transfer of a packed field... 
> >  
> > Any input on that? 
> >  
> > Our user is convinced this can be done somehow by buying some binary 
> > transfer (whatever that woule be) program and letting Oracle
SQLLOADER 
> > do the unpack. 
> >  
> > Ouur contention is that you cnanot do this unless you spend big money 
> > for a transofrmation program like ETI EXtract, Carleton or Prism or
> IBI 
> > to do this for you. Same as you would manually but it automates it.
> ie: 
> > no binary transfer. 
> >  
> > Appreciate anyone who can put this to rest. 
> >  
> The most basic problem here is the different character sets used on the
> different computers. Your mainframe uses EBCDIC and the RS6K uses ASCII.
> If SQLLoader (on RS6K) could read in EBCDIC data, then you might be able
> to get away without conversions. This however is not the case.</DT>
>
> <DT> </DT>
>
> <DT>So, you are correct in what you are doing... unpacking the COMP and
> COMP-3 to just plain text then downloading and having the download software
> do the ASCII->EBCDIC translation.</DT>
>
> <DT> </DT>
>
> <DT>There are gateway type products; Oracle even has one (Oracle Gateway),
> but I hear it is expensive. Depending on your needs, your cobol pgms may
> do just fine.</DT>
>
> <DT> </DT>
>
> <DT>You may use your cobol pgm to sort/summarize data on the mainframe
> (if required) so that the number of rows being downloaded is less. You
> can also get around the necessity of the dot (".") for dollar
> amounts - I believe you can tell SQLLoader that 2 decimals are to be
implied.</DT>
>
> <DT> </DT>
>
> <DT>If you have TCP/IP for the mainframe, you can make use of the FTP pgm
> in batch to automate the download to the RS6k. Watchout for ftp errors
> though. If you don't specify PARM=16 in the EXEC line then any ftp errors
> will still yield a zero RC for the step. The FTP pgm will take care
> of EBCDIC to ASCII translations.</DT>
>
> <DT> </DT>
>
> <DT>If your using fixed format files and are experiencing very large flat
> data files, give thought to making the flat files variable length (comma
> delimited). This needs some tricky COBOL code, but can be done; and can
> save lots of space, depending on the data.</DT>
>
> <DT> </DT>
>
> <DT>Another possibility is the use of sockets to program a pro-c pgm on
> RS6K, and a cobol pgm with CICS-sockets or MVS-sockets to transmit your
> data across a socket. This would be more suitable for real time situations...
> much is possible, depending on your needs.</DT>
>
> <DT> </DT>
>
> <DT>Hope this helps.</DT>
>
> <DT> </DT>
>
> <DT>Zoran.</DT>
>
> </BODY>
> </HTML>
> ------------55993A0E45082--
Received on Fri Feb 28 1997 - 00:00:00 CST