Spanish Ã‘ from iSeries to Oracle

chulett · Post by **chulett** » Thu Mar 04, 2010 8:58 pm

What is the characterset of your target Oracle database?

Boris69 · Post by **Boris69** » Thu Mar 04, 2010 9:05 pm

chulett wrote:What is the characterset of your target Oracle database? ...

I am not at the machine right now, but is I think it is UTF32

Boris69 · Post by **Boris69** » Thu Mar 04, 2010 9:21 pm

chulett wrote:What is the characterset of your target Oracle database? ...

NLS_CHARACTERSET=WE8MSWIN1252
NLS_NCHAR_CHARACTERSET=AL16UTF16

ArndW · Post by **ArndW** » Fri Mar 05, 2010 2:22 am

Perhaps the character is being translated incorrectly from DB2 to DataStage. Isolate the characte and use the SEQ() function to determine which ASCII value arrives in DataStage.

Boris69 · Post by **Boris69** » Fri Mar 05, 2010 9:10 am

ArndW wrote:Perhaps the character is being translated incorrectly from DB2 to DataStage. Isolate the characte and use the SEQ() function to determine which ASCII value arrives in DataStage. ...

I am sorry, I am not sure how to do this

ArndW · Post by **ArndW** » Fri Mar 05, 2010 9:20 am

Change your select to only get rows that have the character in that column. Then find out which position the character is in, i.e. 8, then output SEQ(Input.Column[8,1]) to get the ASCII value as the DataStage job sees it.

Boris69 · Post by **Boris69** » Fri Mar 05, 2010 12:24 pm

ArndW wrote:Change your select to only get rows that have the character in that column. Then find out which position the character is in, i.e. 8, then output SEQ(Input.Column[8,1]) to get the ASCII value as the DataStage job sees it.

comming as 35, which is #

ArndW · Post by **ArndW** » Fri Mar 05, 2010 12:52 pm

What is the character set of your source database, or source table? You need to tell DataStage this value on order for the DB2 read stage to work correctly

Boris69 · Post by **Boris69** » Fri Mar 05, 2010 12:59 pm

ArndW wrote:What is the character set of your source database, or source table? You need to tell DataStage this value on order for the DB2 read stage to work correctly ...

it is 37
I created another table and made it 284, no changes

both times comming as 35

Also, I am usning ODBC stage, sorry forgot to tell

ArndW · Post by **ArndW** » Mon Mar 08, 2010 5:14 am

I don't understand your answer - character set 37 and 284? Are those codesets? What does "GET DATABASE CONFIGURATION" output?

Boris69 · Post by **Boris69** » Tue Mar 16, 2010 2:12 pm

ArndW wrote:I don't understand your answer - character set 37 and 284? Are those codesets? What does "GET DATABASE CONFIGURATION" output? ...

On DB2 on AS/400 (systemi) you can defined field in a table to be certain character set, like 37 and 284

ArndW · Post by **ArndW** » Wed Mar 17, 2010 2:27 am

What character sets do those numbers refer to?

Boris69 · Post by **Boris69** » Wed Mar 17, 2010 12:43 pm

ArndW wrote:What character sets do those numbers refer to? ...

Well, I finaly found a solution:
In ODBC stage I and using Replace function to replcace "wrong" hex value and the converting all to graphic. Not very pleasant but works just fine:

Cast(Replace( F.ABALPH, x'7B',x'69') as graphic (40) ccsid 13488)

thanks for the help