Reading RAW Data from DB2

ray.wurlod · Post by **ray.wurlod** » Tue Jun 26, 2012 5:07 pm

Why do you think that's "non-readable"? I can read it perfectly well in your post.

sreewin7 · Post by **sreewin7** » Tue Jun 26, 2012 5:11 pm

I agree with ray and i was able to read data from db2 to seq file with the same process.

Can you please re check from your end and let us know.

Best of luck.

rohit_mca2003 · Post by **rohit_mca2003** » Tue Jun 26, 2012 11:33 pm

Thanks for replies.

When I am saying that non-readable then I mean to say that data in sequential file should be in ASCII character set and meaningful. Finally this data has to go to Data Warehouse and we can not store data in this format.
Actually DB2 source tables have few field defined as RAW, other fields are either DECIMAL or STRING type. So need help to read RAW data with other fields as well.
Please let me know if I need to set any thing in sequential file stage or need to use any other datatype (other than VarBinary) or any type conversion.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 27, 2012 12:01 am

Might be worth applying a RAWTOHEX function (or the DB2 equivalent).

rohit_mca2003 · Post by **rohit_mca2003** » Wed Jun 27, 2012 12:50 am

Thanks Ray.

Upon checking there is function available in DB2 RAWTOHEX but when I used this function in SQL then I am getting error with job abort.

Process meta data not available in database
Parallel job reports failure (code 139)

When I checked DB2 table definition for RAW column, it is like below:

NAME FOR COLUMN CUSNM CHAR (18) FOR BIT DATA DEFAULT ' ' NOT NULL

Can you please help me how can I define this column in ODBC stage (using ODBC stage to read DB2 data) and in Sequential file stage.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Wed Jun 27, 2012 5:29 pm

How did you define the receiving column (in DataStage metadata) when you used the RAWTOHEX function?

Given that the original is CHAR(18), I'd suggest CHAR(36) or VARCHAR(36) would be appropriate - you'll be getting two hex characters per byte.

rohit_mca2003 · Post by **rohit_mca2003** » Thu Jun 28, 2012 12:42 am

Hi Everyone,

Thanks for your valuable comments. This issue is resolved now, though we have to take different way to resolve.

We were not able to use any function either ROWTOHEX or CAST. May be this is because of we are using (forcibly) ODBC EE stage. Job was aborting.

To resolve this, I have stored binary data in one sequential file and then reding it again as TEXT and export this data as EBCDIC to ASCII. As of now this approach has worked so I will take this as workaround.

Thanks Again.