Re: Midrange data transfers from Mainframe??

Peter Cumming <pcumming_at_ix.netcom.com> 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?????

In general, false. In your particular case, it might work.

SQL*Loader always assumes that non-character number data is in the format native to the platform on which it (Loader) is running. The RS/6000 uses the same byte ordering for binary integers as the mainframe. Packed decimal isn't "native" to RS/6000 at all but I believe Loader expects it to look the same as the mainframe format. This means Loader on RS/6000 probably will interpret such numbers correctly. If you have any floating point numbers (whose formats differ between mainframe and RS/6000) it won't work. If I'm wrong about the packed decimal handling on RS/6000 it won't work. And if you try this on another platform with different number formats (Intel 80x86 is an obvious example) it won't work.

There is also the matter of your character data, which presumably will still be EBCDIC when Loader sees it (because you're taking the mainframe data directly to the RS/6000 with no conversion, right?) Turns out Loader has an option (CHARACTERSET) that lets you specify the character set of the input data when it is other than the default for the platform.

I reiterate that this is *not* the way Loader is intended to work. There is some risk in relying on this approach for production use because Oracle (Support) is unlikely to take on any difficulties you might encounter unless they can be reproduced with a native RS/6000 input file.

>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?

There are two common approaches. One is to write the data to a tape file on MVS (using a utility such as IEBGENER or IDCAMS) and carry the tape to your RS/6000. This means you have to have a tape medium that is usable on both systems, or an intermediate copy step to change media. If you have TCP/IP on your mainframe, you can copy the file using ftp, which has both binary and text modes of operation. One drawback of ftp (with large files) is that it has no restart capability if the transfer is interrrupted. You just start over.

>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.

If you're going to spend money for a software solution, why not license Oracle for your mainframe? You don't have to buy the database product (though we'd love it if you did)...there is an "MVS Client" product set for the mainframe which includes SQL*Net and SQL*Loader. This lets you run SQL*Loader on the mainframe -- where it is guaranteed to understand all native number formats -- and load straight into your RS/6000 database via SQL*Net. All conversions are handled automatically. Loader is restartable if the transfer is interrupted. You can choose either TCP/IP or SNA/LU6.2 protocol for SQL*Net (has to be the same protocol on both sides).

The MVS Client package includes other facilities that might be of interest. Let me know if you want more info.

/b

--
Bill Manry -- IBM Products Division -- Oracle Corporation
BManry_at_us.oracle.com