what to do with a ebcdic 3780 text file

[revilord]

I was given a new text file to import into a SQL database for a client. I thought it was all garbled then I realized it was in ebcdic 3780 format. All I know about cobol is it exists.
I really have no idea what to do with this data. It contains packed decimal data. I have tried using something called iicscvt.exe to convert the data but I still have a bunch of garbage in the text file. Maybe I am using it wrong. I was hoping the the Cobol DBA could send me some converted ASCII data but you know how clients can be.
Can someone please give me some idea of what I can do to import this data properly into a sql database.

 

[3gm]

Hmmm 3780s; haven't seen one in quite a while. Used to produce 8 inch floppies.

Looks like you have (at least) two problems: EDCDIC to ASCII conversion and the presence of packed decimal data in the input.

1. EBCDIC to ASCII conversion.

Assuming you're converting to SQL Server, you can use DTS and modify the data with a VB program/script. See

http://support.microsoft.com/kb/q216399/

for Microsoft's take on EBCDIC to ASCII conversion. Converting to other platforms can be handled similarly just using a plain script of some sort depending on platform. Some platforms have built-in functions/utilities to do this.

2. Packed Decimal Conversion

The presence of packed data means that you CAN'T convert the records in their entirety. You have to convert everything EXCEPT the packed data. The packed data will have to be converted differently and will expand to require as much as twice the size of the incoming field (e.g. a packed decimal field of 5 digits requires 3 bytes; unpacked, it requires 5 [plus perhaps a sign]). You can use a similar technique to Microsoft's to look up two-byte equivalent values using the incoming byte as an index to a 256-entry translation array.

Regards.

Glenn

 

[Glenn9999]

Do you know the format? The main issue I would forsee with a lot of the packed-decimal and binary stuff is that they don't convert properly (example: when this data represents some form of letter in EBCDIC and gets translated to the same letter in ASCII) and get changed into garbage upon conversion.

Probably the best thing to do if it can be done is to try to have someone convert all the data to display-characters on the mainframe side. Then it will convert completely and correctly to ASCII and then you can process it as you need to from there.

 

[revilord]

Thanks, I have seen that as well. But my problem is that I could write a program to do whatever I need to do to convert the data to ASCII but there has to be a better way. If I have to do it for this project then it has been done a 1000 times already and someone would have published the program on the web already.

COBOL is still pretty popular so there must be conversions of COBOL to SQL going on all the time. I work with Oracle and if I was to send data to another I would write a select statement and output it as ascii text and that could be easily imported into any program out there.

Is that not the usual way or when you send COBOL data is it usually sent in EBCDIC and the people at the other end are expected to convert the data into useable format. What other database could use the ebcdic format expect for cobol?

 

[CliveC]

If you have access to someone with Micro Focus COBOL (any version) it has a conversion tool. As long as you know where the packed fields are it is easy to use. I am sure there are others.



Clive

http://tubularity.com

http://PalliativeCareConsulting.org

 

[Glenn9999]

The catch is that I'm not aware of a way to do a perfect one-shot anything goes program to convert a file from EBCDIC to ASCII for the reasons mentioned. Basically with a project like this you'd have to be able to:

1) Swap the orders of all the bytes (PCs are low-endian, mainframes are high-endian) that represent some form of number (or all bytes?). This would also be dependent on the size of the fields involved (a 2 byte field would involve swapping one byte components, but then a 4 byte field would require swapping 2 byte components). As an example, I once processed a mainframe COBOL file in another PC-based language and had to convert the data before I could even process it properly (redefine 4 byte binary field to table of 4 characters, swap 1 & 2 with 3 & 4).

2) Convert all EBCDIC printable characters to ASCII equivalents. Again this would require foreknowledge of the data and how it is formatted.

Basically you have to do specifics for every case and be completely and intimately aware of how the data are formatted. The basic realistic solution that presents itself is to write a simple program that converts all the data to text representations. Then and only then than the "one-shot utility" work and provide correct output.

The only way I've seen this approached is exactly as I have described (programs on mainframe side to convert data file to complete text representation).

Of course if anyone knows an easy one-shot solution to this problem, I would love to hear it myself.

 

[revilord]

Thanks, Glenn. I wanted to go back to the client and ask them to convert it for me. Since I don't know much about Cobol I wanted to know from the experts that this was the thing to do. As much as I like to I don't think I want to be the Hero here and try and convert it.

Thanks everyone for your valuable posts.
Robert

 

[razalas]

Robert,

I agree that if you can get the client to convert the file for you that would be the best since they should know exactly what data is in the file. I would just be sure to specify the particular format you would like to any numeric data to be converted to. In general, if the data is all converted to human readable text, the conversion from EBCDIC to ASCII should be pretty straightforward.

"Code what you mean,
and mean what you code!
But by all means post your code!"

Razalas

 

[WMK]

When talking to the client, request the following:

1) All data should be USAGE DISPLAY (no COMP-3, COMP, BINARY, or PACKED-DECIMAL)

2) All numeric data should be defined with the
SIGN IS SEPARATE
clause

3) The output file should be defined (in the COBOL program) with the
CODE-SET
clause specifying STANDARD-1 (ASCII)

4) The JCL for the tape output file should include the
DCB=(OPTCD=Q)
feature.

***

All of this SHOULD make sense to an IBM mainframe shop. If all of this is done, you will receive a tape file that will be EASILY handled on the PC (or Unix or Linux).

To create your SQL database, you wil probably ALSO need a "record layout" for the file - as COBOL does not have "self-defining" fields.

If the client doesn't understand any of the terms listed above, post here again - and I can provide IBM documentation references for them.

Bill Klein

 

[revilord]

The client is unwilling or unable to convert the data file. I would really like to just give this file to someone and them send it back in ASCII. (This forum doesn't support private messages though). Any suggestions?

 

[3gm]

Contact me offline.

g.mitchell |at| computer.org

Regards.

Glenn

 

[Crox]

Hi,

Conversions of this kind can be done by one single statement: INSPECT <input-varable> CONVERTING <256 byte definition 0 until 255 = ASCII> TO <256 byte definition with EBCDIC equivalent of ASCII>

Of course you can also do this just the other way around when converting from EBCDIC to ASCII.

Regards,

Crox

 

[WMK]

Crox,
Using INSPECT or any similar converions ONLY works if all the data is PIC X. If there is any numeric (with S) and/or COMP, COMP-?, Binary, or Packed-Decimal, then you MUST have the "record layout" to figure out how to convert each field.

PIC X fields get "simple" conversion
Pic S9 Usage Display get just the sign-nibble converted
Usage COMP-? require different conversion (depending on whether you do or do not want "little-endian" output).

Bill Klein

 

[Crox]

So what is the big deal? Convert what you need to convert.... You always need to know the layout. But when going to an other platform, you ALWAYS convert to readable characters! PIC S9(..) SIGN LEADING SEPARATE and such kind of things....

 

[webrabbit]

You always meed to know the layout if there are binary or packed decimal fields in the data, but in Micro Focus COBOL, you just specify the input is EBCDIC and it converts all the display fields but not the COMP(-x) fields.

revilord, let us know whether your problem had been taked care of.

 

[Crox]

That is also possible in CA-REALIA and IBM... but if you prefer to do it yourself or want to do things in a different way than standard ... When you are using special tokens, there is even a big difference between IBM-PC-ASCII and Windows default.

 

[revilord]

Well the file hasn't been converted yet but 3gm is writing a script for me to convert the file. Hopefully that should solve my problem. I don't think I could write a program to convert the file in the time I have left to finish the entire job.

If client was as helpful as everyone in this forum I wouldn't have needed to post on this forum in the first place. Thanks everyone. I will follow up if anything changes.

Robert