Re: SQL*Loader question
Date: 2 Dec 1994 21:45:29 GMT
Message-ID: <3bo4hp$c0k_at_dcsun4.us.oracle.com>
Bill Manry - Oracle Corporation (bmanry_at_upsizeme.us.oracle.com) wrote:
>Steve Blair (smblair_at_rs6000.cmp.ilstu.edu) wrote:
>>We are trying to load a file that was created from a VSAM file on the
>>mainframe. We have to download or ftp it binary because the file
>>contains both binary and packed decimal fields. The character data
>>is being loaded correctly - it is garbage. Can SQL*Loader convert from
>>EBCDIC to ASCII? BTW - the numeric fields all converted correctly. Any
>>help would be very much appreciated.
>Use the CHARACTERSET option:
> LOAD DATA CHARACTERSET WE8EBCDIC37 INFILE...
>This should work assuming you are using codepage 37 EBCDIC (North
>American) on the mainframe.
Another user has pointed out an interesting problem in trying to load "foreign" data directly. If the source and target systems differ in terms of byte orientation (big-endian versus little-endian), binary and packed decimal data will not load correctly. Similar problems will arise with floats if the source and target use different floating-point representation. Steve's successful load relies on the target system being big-endian like the mainframe; it would not work if the target was e.g. 80x86. It won't work with any float data either since his target probably doesn't use S/370 floats.
This behavior is expected. SQL*Loader assumes that input data representation is that of the system on which it executes. That the CHARACTERSET option permits translation from EBCDIC to ASCII is circumstantial; the typical use of that option is to translate between similar sets in the same family, e.g. PC850 and ISO Latin-1, or EBCDIC37 and EBCDIC500.
The "proper" way to load a mainframe VSAM file into an Oracle RDBMS on a different platform is to run SQL*Loader on MVS with a SQL*Net connection to the other RDBMS. In this configuration all data conversions are addressed. This also eliminates problems associated with transporting large files via ftp (space for multiple intermediate copies, lack of restartability of an interrupted transmit). On the down side, you can't use Loader's high-speed "direct" path over SQL*Net.
/b
Bill Manry
Oracle Corp. / Mainframe & Integration Technologies / BManry_at_us.oracle.com #include <disclaimer.usual> Received on Fri Dec 02 1994 - 22:45:29 CET