NLS Settings in DataStage

deepticr · Post by **deepticr** » Mon Mar 22, 2010 11:03 am

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?

ArndW · Post by **ArndW** » Mon Mar 22, 2010 11:23 am

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

deepticr · Post by **deepticr** » Tue Mar 23, 2010 12:57 am

-> 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

ArndW · Post by **ArndW** » Tue Mar 23, 2010 4:49 am

What are your runtime environment settings for NLS_LANG and ORA_NLS?

deepticr · Post by **deepticr** » Wed Mar 24, 2010 1:26 am

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"

ArndW · Post by **ArndW** » Wed Mar 24, 2010 2:21 am

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.

deepticr · Post by **deepticr** » Wed Mar 24, 2010 7:37 am

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?

ArndW · Post by **ArndW** » Wed Mar 24, 2010 8:56 am

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

...

??