Data file conversion from an AS400 (DB2 file) to ASCII in an HP9000

[MAD_2]

We are working on transferring some AS400 DB2 files and then converting them into ASCII so that they may be later on loaded into a database (Oracle/Informix/SQL server, not sure yet). So far this is what we encountered:

1. We FTP the file in binary mode from AS400 (DB2) into HP-9000
2. Run dd to convert from ebcdic to ascii

Problem: The problem we encountered so far is that although it appears the text characters (and dates) are converted, and now readable, some of the fields, which appears that were probably all numbers still encoded and unreadable.

Can anyone tell me about a routine (maybe a C routine) that can complete the conversion in my HP? I have some sample data I ca use to better illustrate what I am talking about.

Anyone's help on a previous similar experience to this one would be greatly appreciated.

[A. Clay Stephenson]

Hi:

You have a fundamental problem if you are trying to do EBCDIC to ASCII conversion on a file that also has binary data at least at a file level. You will need a C routine that only converts the text fields. It may well be that your binary numeric data will require no conversion. I've never done a DB2 conversion so I don't know internal representations. You are going to be far ahead of the game if you can export all the data in some sort of textual format for later import. I do have some C routines that can do these sorts of conversions given byte offsets and lengths.

Regards, Clay

[MAD_2]

All right, A. Clay, we may be able to adapt your C routines, if we figure out the byte offsets and lengths in our specific file(s). The format of the file(s) does not change, so once a routine is specified, the rest should work OK (I hope). Let me see what you have, and thanks for the assistance...

[A. Clay Stephenson]

Hi again:

Okay, here is your basic building block for EBCDIC to ASCII conversion. Let's assume that you have already read a record into a buffer. This buffer should be declared as an array of unsigned char. Now let's suppose that a text field that we wish to convert starts at offset 10 and extends to offset 29.

e.g.

unsigned char my_buff[RECORD_SIZE];
int i = 0;

for (i = 10; i <= 29; ++i)
{
my_buff[i] = ebcdic_2_ascii(my_buff[i]);
}

The first time ebcdic_2_ascii is called, it does some table setup but subsequent calls are very fast.

This should get you started and hopefully the numeric field are represented the same on both platforms. If not, supply a bit of data with known values and we will see what can be done.


Regards, Clay

[harry d brown jr]

You are going to run into "packed" fields and such, where its probably a lot easier to export the data in ascii delimited format. Follow this link for help:

http://nscpcw.physics.upenn.edu/db2_docs/db2help/ttaex101.htm


live free or die
harry

[MAD_2]

Harry,

The problem is that we are not getting any DB2 export support. They do not want to add up any more processing time to the AS400 and we are trying to get this done on our side. Additionally, the files we will be FTPing are 10-15 GB in size. The perfect scenario calls for a 1:15 - 1:30 hr file transfer and then a local conversion in our HP, that's where the tricky part comes in.

[harry d brown jr]

Mynor,

Can you get a temporary license for DB2 for hpux from IBM?

live free or die
harry

[Bill Hassell]

The task is going to be a BIG job. You will need detailed field definitions for every record type. This includes understanding the bit and byte order of numbers. ASCII/EBCDIC is easy. But there are lots (as you've seen) data elements that must be converted, with each field requiring an accurate definition (ie, bit length, endian style, signed or unsigned, etc). There may even be specialized data types.

So plan for a significant conversion project (several days) which includes all the research on the data formats. (perhaps using the data export function in DB2 looks more attractive?)

[MAD_2]

Bill/Clay (and anyone who can help!!!)

I have been exposing this situation to an HP engineer, and he provided the best findings so far. Here are his findings with a one record sample:

Output from dd (dd if=RGUARTP.binary of=RGUARTP.dd conv=ascii):

BT Subterrean Retreatment Bait
Y~^A^GYN^ONNT^_M-^U-^O^OM-%^OYYNN^A^_^_RWINGET 2001-03-2715.
12.37RWINGET 2001-07-2311.22.43

This is what the one record really contains:

BT Subterrean Retreatment Bait
Y 5 12 Y N 0 N N T .01 356
0 460 Y Y N N 11 1 RWINGET 27-MAR-2001 15:12:37
RWING
ET 23-JUL-2001 11:22:43

Numeric values are our problem, they are packed. He also provided the following layout of the first values in the record that could not be converted:

Using a binary dump he found the "Y512YN" string to see how these values are encoded, this is what he found:

E8 005F 012F E8 D5
| | | | |->D5=N in EBCIDIC
| | | |->E8=Y in EBCIDIC
| | |->012F=12 in packed format
| |->005F=5 in packed format
|->E8=Y in EBCIDIC

And gave us a resource site:
http://www.csgnetwork.com/ascebcdic.html

So the main issue now is that we have find (or write) some routine to translate the DB2 record entirely to EBCIDIC before we use dd
to convert it. Anyone has one previously used? Similar scenario?

Thanks everyone,

[A. Clay Stephenson · Accepted solution]

Hi again:

I'll try once more. Forget about using dd as a conversion tool. That is only adding distortion to a situation that is already bad. The problem is that a dd conv=ascii is an all-or-nothing proposition. You want the EBCDIC-to-ASCII conversion to only operate on strings/character fields. If you also apply it to numeric fields does it may convert to ASCII or it may be a byte value for which there is no ASCII equivalent. You are going to have to do this on a field by field basis.

You can use dd to transfer of reblock data but not as a translation tool is this case.

[Tommy Brown]

Mynor,
My 2 cents.. It appears to me that you need to export the data to get it to text(EBCDIC would be ok and convertable to ASCII on the UNIX side). I think it would be to difficult to handle the "low" level characters of the "raw" data. Harry mentioned a License of DB2 for HPUX, either that or beg, borrow, or steal some AS400 time. A alternate thought would be to have a third party firm do the data export...
Good Luck,
Tommy

[MAD_2]

Clay,

You have mentioned here a routine for EBCDIC to ASCII conversion....

Can you tell us in in what libraries is the ebcdic_2_ascii function located so that we may recall it? Or can you get us a copy of the routine or where we may be able to get it? Someone must have done this already in the past.

Thanks.

[A. Clay Stephenson]

Hi:

I attached it to my earlier posting. Simply open the attachment and there is the C source.

[MAD_2]

Thanks Clay, missed it the first time.....

[harry d brown jr]

Mynor,

May I suggest this product to help you convert the data:

http://www.iri.com/public/solutions/netconvert/netconvert_desc.htm


live free or die
harry

[Vijender Sandella]

I transfer data from DB2/AS400 to Oracle database on HP9000, on a regular basis.
I use a tool called "Data transfer from AS400" from IBM. I use version 3, release 2. There might be a better one available now. In this there is a facility to convert the data while transfering from DB2/AS400 to any destination you choose. My Oracle database(datawarehouse), is connected to AS400/DB2 through Gateway.

VJ.