Re: Converting Packed/Decimal numeric from mainframe to ASCII text.

Joe wrote:

> Does anyone know of a way that I can convert data from the mainframe
> to ASCII fixed?

If it is a straight forward EBCDIC to ASCII conversion, then use the dd command as Peter suggested.

However, the thread title refers to packed numeric format. This means to me you are dealing with something like COMP or COMP3 numeric formats generated by COBOL programs or the like (i.e. binary numeric mainframe formats).

The dd conversion simply converts an EBCDIC char into an ASCII char. It can not convert a 6 char COMP3 EBCDIC into a 8 char ASCII display field containing implied decimal and sign.

> I am working on an Oracle 8.1.7 database on a UNIX
> platform and need the data converted so I can load it into Oracle via
> SQLLDR.
If you are dealing with packed numerical (binary) formats, then you basically have two very obvious choices.

Do the conversion from packed numeric fields to alphanumeric display fields on the mainframe. This is often pretty easy to do as writing a COBOL program to perform the conversion is extremely easy. Use dd to convert the mainframe EBCDIC to ASCII code. The resulting data file is a text file that can be loaded via SQL*Loader using a fixed record length control file.

Do the conversion on Unix/Oracle. This is the more complex method. The datafile that you now have on Unix, is not a character file, but a binary one. You can write C software to perform the conversion from binary to text. You can look at the SQL*Loader binary load options (which do support certain foreign binary formats). You can load that data as raw data into Oracle and then attempt to perform the conversion using JAVA (doubt that PL/SQL will be suitable for this).

I've used a combination of these two method myself. We converted the numeric stuff to text on the mainframe by writing a few COBOL programs. However, as the data was from a mainframe database dump, we left the physical binary rowids intact. That was loaded as hex data into raw columns and simply used to determine father-child relationships in the data we loaded (the data being from a hierarchical mainframe database). It worked pretty well.

--
Billy