Data Conversion Hex to Char/ Numeric

shrey3a · Post by **shrey3a** » Tue Nov 23, 2004 11:30 pm

Hi,

I'm extracting data from DB2- AS400 thro' ODBC stage.

The data I'm looking at is in format

Column 1 (Hex Format) Output D
F0F0F0F9F9F9 000999

Column 2 Output
C8D9 HR

I need to changed the value of the Input(source) to desired Output

* I even converted the Input data to Binary format and used functions XTD , MXOC, MY, XTD, ASCII , Char, EbscdictoAScii , Pack decimal routines for Comp1 comp3. But nothing works as data is in Hex format and even changing it to Binary the desired characters are not been produced i.e A to Z , In first case I get the last charcter by using the char function i.e if desired output is 0001 i get 1.

Do I need to do something with ODBC connection to convert Binary to text . Datastage enviorment is Ver 6.0 on Unix HP 11i.

Can anybody help on it.

Regards,
Munish

ray.wurlod · Post by **ray.wurlod** » Wed Nov 24, 2004 1:06 am

Try Iconv with "MX0C" (a zero, not a letter).

shrey3a · Post by **shrey3a** » Wed Nov 24, 2004 8:24 am

Hi Ray ,
I tried it with Ans= Iconv(Arg1,'MX0C')
also woth Oconv

But still its not working ...do we have some routine to convert Binary or hex format to Char or Numeric. Char() function works but will pick up only the last bit and display it i.e 0009 will turn out to be 9

[quote="ray.wurlod"]Try Iconv with "MX0C" (a zero, not a letter).[/quote]

ray.wurlod · Post by **ray.wurlod** » Wed Nov 24, 2004 2:39 pm

What would you expect to see? 0xF0 and 0xF9 are non-printing characters. (I am assuming here you are using ASCII - that is, that you do not have NLS enabled.)

shrey3a · Post by **shrey3a** » Thu Nov 25, 2004 12:34 am

Hi ray,

I need to conver the Hex to Char

Below is the example F9 is hex format for and its conversion in ascii is 9
same for the 2 column

Column 1 (Hex Format) Output D
F9F9F9 999

Column 2 Output
C8D9 HR

You said I need to check my NLS tab for that can you suggest which NLS I need to select .

[quote="ray.wurlod"]What would you expect to see? 0xF0 and 0xF9 are non-printing characters. (I am assuming here you are using ASCII - that is, that you do not have NLS enabled.)[/quote]

Mike · Post by **Mike** » Thu Nov 25, 2004 11:20 am

I think your hex "F9" is likely an EBCDIC character. I think your AS400 DB2 uses EBCDIC natively. Check the character set specified in your .odbc.ini file. I think the charset should be set to 37 for EBCDIC.

Mike

shrey3a · Post by **shrey3a** » Thu Nov 25, 2004 4:01 pm

Hi,
Yes , I checked at DB2 end AS400 DB2 which uses EBSCDIC.
There F9 conversion is 9 , lease advice the steps I should go forward to
convert it in to readable numeric/ characters for example

F9 = 9
C8D9 = HR

One step is I should change the ODBC file characterset to 37 (can you confirm as I have no idea on this i mean 37 is one i have to use).

And rest I can go for which function i.e. first to change it to ascii() and then in next transform stage I should go for char()
for character fields.

Thanks in advance ,

Regards,
Munish
[quote="Mike"]I think your hex "F9" is likely an EBCDIC character. I think your AS400 DB2 uses EBCDIC natively. Check the character set specified in your .odbc.ini file. I think the charset should be set to 37 for EBCDIC.

Mike[/quote]

Mike · Post by **Mike** » Fri Nov 26, 2004 8:21 am

Hi Munish,

37 is indeed the code page for IBM's EBCDIC character set. You can verify that with a Google search. What is the character set specified in your .odbc.ini? My experience is using ODBC to connect to DB2 on OS/390 (which is also EBCDIC). There was no need to do any conversion from EBCDIC to ASCII. I assume the ODBC driver must handle the conversion automatically when the correct character set is defined for it.

Mike

shrey3a · Post by **shrey3a** » Fri Nov 26, 2004 7:49 pm

Thanks Mike ,

I'll implementthe sol on Monday and will update you with the result.

Regards,
Munish

[quote="Mike"]Hi Munish,

37 is indeed the code page for IBM's EBCDIC character set. You can verify that with a Google search. What is the character set specified in your .odbc.ini? My experience is using ODBC to connect to DB2 on OS/390 (which is also EBCDIC). There was no need to do any conversion from EBCDIC to ASCII. I assume the ODBC driver must handle the conversion automatically when the correct character set is defined for it.

Mike[/quote]

shrey3a · Post by **shrey3a** » Sat Nov 27, 2004 11:06 am

Hi Mike,

Can you send me the entry/screenshot of characterset 37 for escdic character from your ODBC.ini file . Will be helpful as I'm new to this concept. If screenshot is not possible on forum then you can send me on muneesh.sharma@pb.com

Regards,
Munish

[quote="shrey3a"]Thanks Mike ,

I'll implementthe sol on Monday and will update you with the result.

Regards,
Munish

[quote="Mike"]Hi Munish,

37 is indeed the code page for IBM's EBCDIC character set. You can verify that with a Google search. What is the character set specified in your .odbc.ini? My experience is using ODBC to connect to DB2 on OS/390 (which is also EBCDIC). There was no need to do any conversion from EBCDIC to ASCII. I assume the ODBC driver must handle the conversion automatically when the correct character set is defined for it.

Mike[/quote][/quote]

Mike · Post by **Mike** » Sun Nov 28, 2004 5:01 pm

Here is the relevant setting from the .odbc.ini file. Note that we were using the DB2 Wire Protocol driver (also NLS was not enabled).

Code: Select all

ServerCharSet=37

The default ServerCharSet was 1252.

Mike

shrey3a · Post by **shrey3a** » Mon Nov 29, 2004 9:48 am

Hi Mike,

We are in process of changing the ODBC file on our UNIX box for Datastage server to map to charset 37 as suggested by you and wanted to know that also if the AppCodePage has to be changed to 37.

Regards,
Muneesh

Mike wrote:Here is the relevant setting from the .odbc.ini file. Note that we were using the DB2 Wire Protocol driver (also NLS was not enabled).
Code: Select all
ServerCharSet=37
The default ServerCharSet was 1252.

Mike

Mike · Post by **Mike** » Mon Nov 29, 2004 11:23 am

We didn't change that setting. I don't know how that setting would be used by DB2/DataStage. Leaving it unchanged did not cause us any problems.

Mike

shrey3a · Post by **shrey3a** » Tue Nov 30, 2004 8:39 am

Hi Mike,

Nothing worked at our end by even changing thecharcode to 37.

The daya in AS400- DB2 is in Ebcdic (Hex) format and we needed to change it to Ascii(Char).

Do we have any other suggestion which can be implemented.

Regards,
Muneesh

shrey3a wrote:Hi Mike,

We are in process of changing the ODBC file on our UNIX box for Datastage server to map to charset 37 as suggested by you and wanted to know that also if the AppCodePage has to be changed to 37.

Regards,
Muneesh
Mike wrote:Here is the relevant setting from the .odbc.ini file. Note that we were using the DB2 Wire Protocol driver (also NLS was not enabled).
Code: Select all
ServerCharSet=37
The default ServerCharSet was 1252.

Mike

Mike · Post by **Mike** » Tue Nov 30, 2004 12:22 pm

Sorry. I don't have any AS400 DB2 experience. Only OS/390 DB2 using the DB2 Wire Protocol driver. If you can't get the ODBC driver to deliver ASCII data to you, you could try BASIC's ASCII function in a transformer to translate the characters for you.

Mike