DSXchange

Please look as your destination with something other than View Data. Tell us what's actually loaded. The question mark is simply a mechanism for representing "unmappable character".

ray.wurlod wrote:Please look as your destination with something other than View Data.

Ray, It also loaded as question marks (?) at the destination when querying the Oracle 10g destination.

What tool did you use for the query? What do you see when you use TO_HEX() in the query?

sohasaid,

You stated that the two characters listed in the subject are coming in from
Oracle in a US7ASCII table.

Those characters are not in the 7-bit US ASCII table and therefore are not displayable and changed to '?'.

ArndW wrote:Those characters are not in the 7-bit US ASCII table and therefore are not displayable and changed to '?'.

It seems reasonable. I don't know how these data are inserted into the database but I've to transfer it even it was a rubbish data.

Any Ideas?

The issue is that you don't know what character set you are using in your database (the 7 bit one is patently incorrect), so you won't be able to get DataStage to read it correctly using NLS. Go into DB2 and enter 'get db cfg for {your database};'

ArndW, my data source is Oracle RDB not DB2 and I'm quite sure that its character set is US7ASCII.

Ok, then your source is Oracle. The two characters you've listed cannot be stored in 7bit ASCII, internally your Oracle is using another character set. You've specified that Oracle converts from the internal character set to 7 bit ASCII and it is there that you are losing these character values. Find out, from your DBA, what character set is being used for this table and set your environment variables accordingly.

The Character set enabled at the entry device(Keyboard) may have latin accents enabled. The data entry person can then enter these into the GUI and the DB will compress the 8Bit data into a 7Bit field (Lossy data). This data will then appear to be wingdings, Arabic, upside down question marks etc. I have encountered this problem twice before. The SQL tool, DataStage will Select and Read the data as is (Lossy) but the target DB will reject the data with error messages like data inserted is to large for field definition. To date I have not figured out how to overcome this aside from "cleansing" the data at the source.
Both clients have refused to convert their source DBs to an 8Bit NLS.

Give your clients a list of the ASCII7 characters, with the advice that their decision means that these are the only characters that can be processed.