DSXchange

Hi,

We are trying to read data from some oracle source databases and load it into our staging area. There are no transformations being applied, it's a on to one mapping. The target tables are also oracle.

When executing this job we are encountering some warnings while trying to read data.

"Invalid character(s) ([xA3]) found converting string (code point(s): XXX [xA3]10M ) from codepage UTF-8 to Unicode, substituting. [nls/converter.C:1090]"

The data in one of the rows is "XXX <pound_symbol>10M"

The project deafult setting for NLS is UTF-8.

We find that in the DB we have special characters like the "pound" symbol. We tried by giving ISO 8859-9:1989 setting on the source oracle enterprise stage, in which was also in vain.

Could some one please address this issue?

What stage are you using for Oracle and what are your NLS environment settings for Oracle?

-> Source DB NLS setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET =AL16UTF16

-> Target DB NLS Setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET AL16UTF16

-> In the Datastage job:
ORCL_SRC_STG ---> XFM ----> ORCL_INSERT_STG

I'm using using oracle enterprise stage to read data. NLS Setting for the stage = UTF-8 (project default)

I have configured the target oracle enterprise stage as follows:
Upsert Mode = User-defined UpdateOnly
UPDATE SQL = INSERT INTO STG_TBL ....

There are 5 records from the source which have spl characters and 4 are getting inserted into the target, except one. The error I get is "Unable to use a record for update"

The staging table that I'm trying to insert into doesn't have any constraints on it.

-Deepti

What are your runtime environment settings for NLS_LANG and ORA_NLS?

I checked for the NLS_LANG in the dsenv file

NLS_LANG = American_America.WE8ISO8859P1

and the source and target DB have the following NLS parameters

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1

I'm not sure where I need to check for the ORA_NLS? It's not there in the dsenv file.

I tried giving the NLS setting of ISO_8859-1:1987 in both the source and target oracle enterprise stage. But, it still seems to be throwing the same error "Unable to use a record for update"

Your data is stored in UTF16 in the database so it would be best to set your NLS_LANG to the same and also specify that in your Oracle stage.

Hi,

The NLS_CHARACTERSET in the DB is also WE8ISO8859P1. Doesn't this map to ISO8859P1 in Datastage?

The project level NLS setting in Datastage was UTF-8, which I now changed to ISO8859P1. But, the error still persists.

Any thing else that I seem to have missed?

deepticr wrote:-> Source DB NLS setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET =AL16UTF16

-> Target DB NLS Setting: (select * from nls_database_parameters)
NLS_NCHAR_CHARACTERSET AL16UTF16

...

??