AS400 Packed Data

virginie · Post by **virginie** » Thu Jan 22, 2004 2:57 am

Hi all
It is my first time in this forum...
We have some problem extracting packed data from our AS400 (condensed strings). We tried some routines in the SDK\DataTypes but with no success...

Has someone already encoutered this problem?

Thanks in advance,

Virginie

roy · Post by **roy** » Thu Jan 22, 2004 5:25 am

Hi,
you said:

We have some problem extracting packed data from our AS400 (condensed strings). We tried some routines in the SDK\DataTypes but with no success...

How is this data represented when you unload it to a file (on the AS400 system)? is it binary format?
are you using DB2 ?
can you give a sample of what you'r doing?

virginie · Post by **virginie** » Thu Jan 22, 2004 7:06 am

Hi, Thanks for your quick answer!
I will try to be a bit clearer :
a field is packed on our AS400 and when we try to get the content of this field via Datastage - ODBC Stage into a column type "char" , we can not read this data, we only obtain some pipes '||||', the numeric are packed and are not depacked by datastage.

it is EBCDIC format...

Thanks

Virginie

peterbaun · Post by **peterbaun** » Thu Jan 22, 2004 8:11 am

Hi -

Try using some of the built-in transforms for converting data from EBCDIC to Ascii.
Especially I would think that the transform "DataTypeEbcdicToAscii" could help.

Provided that you use Client Access as your ODBC connection have the following in consideration -

Don't know if it is a problem but since you have this kind of datatypes in your AS400 also consider if you in the ODBC Data Source Administrator need to enable "Convert binary data to text" (done in the Translation tab)

Also make sure that you in the ODBC Data Source Administrator have configured the following. Go to
General-->Connection Options-->
in here you must enable "Never Prompt for SQLConnect"

If you don't do this DataStage will hang when you try to access data and have entered a wrong password.

Hope that some of this helps

Regards
Peter

virginie · Post by **virginie** » Thu Jan 22, 2004 10:38 am

hi
I put all what you said to me but did not find the "DataTypeEbcdicToAscii" routine...
can I download it from somewhere?

Thanks

Virginie

crouse · Post by **crouse** » Thu Jan 22, 2004 11:30 am

If the data is truely packed, EBCDIC to ASCII conversion won't work. You need to know HOW the data is packed in order to use the appropriate routine to unpack it (and hope the ODBC stage isn't messing up the data on retrieval).

Several routines exist to convert packed data, such as DataTypePicComp1.

look under Transforms\SDK\DataType for the others

shawn_ramsey · Post by **shawn_ramsey** » Thu Jan 22, 2004 11:56 am

virginie wrote:Hi, Thanks for your quick answer!
I will try to be a bit clearer :
a field is packed on our AS400 and when we try to get the content of this field via Datastage - ODBC Stage into a column type "char" , we can not read this data, we only obtain some pipes '||||', the numeric are packed and are not depacked by datastage.

it is EBCDIC format...

Thanks

Virginie

I find that very odd. We have just converted over to using the iSeries ODBC driver to pull data from the AS400 and the data type for the columns came through without any conversion required. When I imported the structures to DataStage the columns came through as Decimal and Numeric. I am surprised you need to convert anything.

virginie · Post by **virginie** » Mon Jan 26, 2004 4:41 am

Hi Shawn,
the problem is that the source column is alpha-decimal but some subtrings are decimal and can be packed. That is why, I think, datastage does not convert anything because source is char(200).

The problem still persists : I tried to create my own routine (oconv (xxx, MP) ) but without success... If anybody encountered the problem, please respond...

Thanks in advance

Virginie

shawn_ramsey · Post by **shawn_ramsey** » Mon Jan 26, 2004 10:07 am

virginie wrote:Hi Shawn,
the problem is that the source column is alpha-decimal but some subtrings are decimal and can be packed. That is why, I think, datastage does not convert anything because source is char(200).

The problem still persists : I tried to create my own routine (oconv (xxx, MP) ) but without success... If anybody encountered the problem, please respond...

Thanks in advance

Virginie

Is the char(200) what you entered or was it the result of importing the table metadata for ODBC?

If the field is defined as char through the ODBC driver than I would suspect that the driver is doing an EBCDIC to ASCII conversion of your packed data and therefore the binary representation of that data is now not in the proper format for a Packed field. We have had folks do an ASCII ftp and have ran into the same issues. You can try to convert the data back from ASCII to EBCDIC before running the packed conversion on it but I would suspect that it will not work, since the ODBC and Ascential conversions won't handle the Binary values that lie outside the EBCDIC definition in the same manner.

Somehow you need to keep the ODBC driver from doin the EBCDIC to ASCII conversion for you.

inter5566 · Post by **inter5566** » Tue Jan 27, 2004 9:35 am

Virginie,
Are you using the ftp stage to retrieve the data from the AS400? Is the packed data similar to this example:

base 10 # expanded hex value virticle by byte followed below

+12345 =

135 (3 byte source field)
24C

or

0135 (4 byte field)
024C

If you answer yes to both questions, then the solution is to select binary transfer method in the ftp stage. Then define the source columns by their source byte length i.e. +12345 packed = 3 characters not 6, also make sure you fill in the "display" column with the same value as the length. Now run the ftp stream into a transform and put the SDK conversion routines in each derivation on the output. Use the EBCDICtoASCII for character fields, and the datatypepiccomp(and others) for the numeric and packed fields.

HTH,

Steve