Reading Packed Decimal Data from Db2 table

Inquisitive · Post by **Inquisitive** » Mon Oct 30, 2006 5:20 pm

Hi All,

I have posted this before, I am posting with some more information and a little different scenario, which could help me to get solutions faster.

I am trying to read DB2 table (Db2 on mainframe) which has Packed decimal data (Comp-3) stored in CHAR (Char2) field. I am using Db2 API stage to read data.

When I import table definitions, the field is defined as CHAR and 'view data' shows some un-readable data in the field. (I think these are Hex values)

I tried using function DataTypePicComp3 in transformer but it did not give proper results. It did uncompress but when I compared data against table it was not correct.

I am able to see the data in hex format in Mainframe.

Some sample data examples are

05156F
99125F

(F suggesting that it is not a signed decimal, This is readable when I log-in to mainframe and then turn Hex on)

Any solutions to this problem would be very helpful.

Thanks

Precious · Post by **Precious** » Tue Oct 31, 2006 12:10 am

FYI

I was on a project where we were sourcing from DB2 on mainframe.
But in our case, we got data dumps from the mainframe in the form of flat files. These we read using the complex flat file stage. This stage has the relevant datatypes to allow you to read packed decimals. I know this is not a solution, but it could be an alternative.

Regards,

Inquisitive · Post by **Inquisitive** » Tue Oct 31, 2006 10:41 am

Thanks for your comments Precious.

I have option of using CFF stage as well. I can take a dump of tables into a flat file and then read data through CFF stage.
But like I said source field is defined as Char(2) how do I set up CFF stage to read this comp-3 field? I tried setting up this field as decimal and then in selcting 'comp-3' in 'usage' tab of 'edit row' window for that particular column. It did not solve the problem either.

Also I tried usgin routine 'DataTypePicComp' which produced some negetive values, I am not sure if I am missing something here.

When I log-in to mainframe and turn 'Hex On' I see following values in the source table ..

Col1 Col2

005F 01353F
006F 02447F

Any pointer how we can read that? either using CFF or using SDK routines?

Thanks

Inquisitive · Post by **Inquisitive** » Tue Oct 31, 2006 7:02 pm

Here is the resolution to this problem,

I used SDK routine DataTypePicComp3 to convert comp3 into integer. I got it working only after reading the packed decimal field as BINARY from the source though it is defined as CHAR in the table. I just went ahead and changed datatype from CHAR to BINARY in DB2 API stage..............

..........Then they lived happily ever after :D

ray.wurlod · Post by **ray.wurlod** » Tue Oct 31, 2006 8:47 pm

And you were so joyful that you marked the thread as Resolved!