Mismatch in data read in DB2 AS400 using SQL client and DS

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
mehimadri_123
Premium Member
Premium Member
Posts: 3
Joined: Sat Aug 26, 2017 7:55 pm

Mismatch in data read in DB2 AS400 using SQL client and DS

Post by mehimadri_123 »

Hello Everyone,

I am reading a DB2 AS400 Table using DataStage (v11.7) DB2 Connector Stage and writing it out to a peek stage (will finally write it off to a flat file on S3). Now, while all the other tables that I have read so far, I could verify that the data was loaded correctly from DB2 to target (peek), for one particular table while we can see correct data in DB2 using a SQL client tool, we do not see the same in DataStage peek stage/flat file.

Here is what I see in DB2 using a SQL client tool:
PAYFPS
===============================
|0000000012||NET60|60|REMIT|


This is what I see in DataStage:
PAYFPS
================================
4FF0F0F0F0F0F0F0F0F1F24F4FD5C5E3F6..............

I tried to match the code pages in DB2 Server and DB2 Client on DataStage. The code page on DB2 Server is 37 (EBCDIC code page). When I try to set the same in DataStage using the environment variable, DB2CODEPAGE then it tells me that the DB2 environment cannot be initialized. I can however set this variable to other values such as 819, 1208, 1251, 1252 but it does not help as I continued seeing the same data as above.

If anyone in this group has encountered this problem before and/or know how to solve it then please let me know.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Well, that's not really a mismatch. Iin case you're not aware, that data you're seeing is the EBCDIC version of the ASCII posted above it. What is your goal here, to actually write it out in ASCII to the target S3 file?
-craig

"You can never have too many knives" -- Logan Nine Fingers
yugee
Participant
Posts: 34
Joined: Fri Feb 04, 2011 5:54 pm

Post by yugee »

Can you post the column definitions in DB2 and DataStage? Most of the SQL clients convert the data before presenting, where as DataStage will display the values based on the definition.
Post Reply