conversion of data from ascii to ebcdic

kattineni · Post by **kattineni** » Tue Sep 14, 2004 12:26 pm

Hi ALL,
I am converting data from ascii format to ebcdic format.My source is oracle table and i am loading the ebcdic data into a sequential file so that the file can be ftp'ed to AS/400 system.The problem is that for some of the fields in the target AS/400 the datatypes are packed.So when the file is viewed in the AS/400 system i do not have the exact conversion for those fields with the datatypes as packed.Is there any way that i can convert the unpacked fields in Oracle to the packed fields in the target file using datastage.so if anyone can help me out with this issue,i would greatly appreciate it.

Thanks
vijay

ketfos · Post by **ketfos** » Tue Sep 14, 2004 2:45 pm

Hi,
What routines are you using for data transformation from ascii to edcdic?
what values for failing the conversion?

Ketfos

kattineni · Post by **kattineni** » Tue Sep 14, 2004 4:12 pm

Hi ,
i am just transforming the data from ascii to ebcdic formats using the ebcdic conversion dsfunction.other than that i am not using anyother specific routines.the conversion looks ok for all the char,varchar2 fields except for the packed decimal fields.The datatype for these fields is numeric.For all the other fields which are not packed in the as/400 the data looks ok.due to these packed fields the data in the as/400 looks scattered.since my source is a database table,suppose a field has a length of 17,when it comes to the as/400 the field length is only 9 for the same field(since it is packed).
My record length in the source is 423 where as the record length in the as/400 is 400 due to these packed fields.
Hope if this info can give you a better understanding of this issue.
thank you
vijay

flashgordon · Post by **flashgordon** » Thu Sep 16, 2004 6:01 pm

Hi,

I was going to suggest outputting to a complex file format. complex file format understands packed pretty well. but i just tried it and cff is input only. unless someone knows of a 'pack' routine in datastage it's going to be pretty painful. remember ibm type machines use packed in machine instructions but it is very foriegn to most other machine types. It might be simpler to put out clean ascii with trailing sign bytes for the numbers and use the as400 to do the pack.

... Tom

vmcburney · Post by **vmcburney** » Thu Sep 16, 2004 8:51 pm

You can output to mainframe file formats if you get DataStage MVS. Do you have a spare quarter of a million bucks lying around?

chulett · Post by **chulett** » Thu Sep 16, 2004 9:39 pm

I'm going to chime in here with Flash and Vince. Just because the target data types are packed decimal doesn't mean you need to supply your export file with packed decimal fields in it. Heck, there's not really even a burning need to provide it in EBCDIC, either.

Many FTP programs can do the conversion on the fly for you. Or they can, on the target system.

Get your AS/400 folks to cut you some slack. Sheesh.

Mike · Post by **Mike** » Fri Sep 17, 2004 6:53 am

First, I agree with the consensus. It should be much easier for the AS/400 to deal with unpacked numbers than it will be for UNIX to deal with packed EBCDIC numbers. As Craig indicates, if you create all of your output as text, then the ftp can handle the ASCII to EBCDIC conversion for you.

If you can't get your AS/400 folks to put in a little effort to deal with unpacked numbers, then you could probably put in a lot of effort to create yourself some custom routines/transforms. Take a look at the SDK routines/transforms that Ascential provides to go from packed EBCDIC to unpacked ASCII. You will have to create a set to reverse the process.

Mike

kattineni · Post by **kattineni** » Fri Sep 17, 2004 12:26 pm

Hi all,
Thanks for your replies.Yes as you suggest it is going to be very difficult in dealing with the packed decimal through datastage alone.I tried different options in using datastage sdk routine transformations as Mike suggested but not much of the result could be seen.
Thank you all
vijay

chucksmith · Post by **chucksmith** » Fri Sep 17, 2004 1:25 pm

Is ascii or ebcdic an issue at all for packed fields?

If you record has packed data in it, then you should be ftping it in binary mode.

Ascii and Ebcdic refer to you string and numeric fields, not you packed fields. Binary in packed fields is binary.

You will probably have to deal with this on a column level with the Ebcdic() function.

ogmios · Post by **ogmios** » Sat Sep 18, 2004 3:52 am

From experience I would say it's much easier to do all of the packing stuff on the AS400 in custom made (non DataStage) jobs.

Have your mainframe people write some small libraries to pack data upon arrival of the file, it will save you a few gray hairs.

What also happens a lot is that later on in life another system needs the same file (for a reengineering project e.g.) and this then needs the unpacked data.

Ogmios

P.S. It also works the other way around, I would also have the AS400 unpack its data before sending it to the DataStage server.