How to change NLS settings at runtime

sundar · Post by **sundar** » Mon Jan 21, 2008 3:30 pm

Hi All,

The datastage is installed in hp itanium server.we are getting sources from different source systems, each one having different NLS setting for each source database.

We have arabic data that needs to be loaded to our DWH system,so we need to change the NLS setting at runtime.Please let us know how to change the NLS setting at runtime in unix,without restarting the Engine.

Thanks & Regards
Sundar

ray.wurlod · Post by **ray.wurlod** » Mon Jan 21, 2008 8:12 pm

I assume that, by "NLS Setting", you mean "character map".

Surely there can't be that many different character maps? After all, there's only one for Arabic.

Can you not create "clone" jobs with different NLS settings for the few different character sets that you will be processing and, in a job sequence, perhaps, identify the source system and invoke the appropriate job?

sundar · Post by **sundar** » Tue Jan 22, 2008 12:32 am

Thanks for your prompt reply... Would appreciate if you can help us in the below issue. Please let us know if more information is required.

Issue:
The problem is we are working on different database systems with different NLS setting done at the database level. Say for example, one of the source database is Oracle with NLS_CHARACTERSET set to AR8ISO8859P6 while other Oracle source database has characterset set to US7ASCII.

We can create separate jobs for fetching data from both this databases. But by default, NLS_LANG set in dsenv is US7ASCII. So, only one extraction works fine and retains the Arabic characters. While the other job extracting data from database having AR8ISO8859P6 characterset gets conflicted with the NLS_LANG defined in dsenv and it losses it's Arabic value. The data gets converted into some junk characters.

We need to know how can we change the NLS_LANG in the dsenv which by default is US7ASCII to change to AR8ISO8859P6 at runtime without restarting the DSEngine. Basically need to override the Environment Variable setting at runtime....

Thanks,
Sundar

ray.wurlod · Post by **ray.wurlod** » Tue Jan 22, 2008 1:31 am

Bring NLS_LANG into the project as an environment variable (you do this in the Administrator). Then you can use it in DataStage jobs as a job parameter and set it appropriately for each indivvidual job. The same is true for any other environment variable that may apply, such as LANG_C.

sundar · Post by **sundar** » Tue Jan 22, 2008 5:21 am

The issue was solved thanks for your timely help.

ray.wurlod · Post by **ray.wurlod** » Tue Jan 22, 2008 7:45 am

Then please mark the thread as Resolved and post a note indicating precisely how you solved it, so as to help future searchers after answers.

sundar · Post by **sundar** » Wed Jan 23, 2008 10:53 am

We included NLS_LANG as User Defined Environment variable and included the same in our jobs.

The above resolution works fine is I am extraction data from different databases (say Oracle for example) having different character sets and dumping the same into a dataset or flat file. Then read this dataset or flat file with different character set (as per the target database) and load into the target. The data is retained as is.

But the problem now we are facing is for jobs that are directly extracting from one database and loading into another database having different character set in the same job. As per the solution above, we can use only one NLS_LANG in the job which would support either of the databases. In this case we are lossing the data character type. So, either our extraction or load fails.

How to handle different characters (in different stages, in same job) ?

Kindly provide inputs how to resolve the same.

Thanks & Regards
Sundar

just4geeks · Post by **just4geeks** » Wed Jan 23, 2008 12:35 pm

ray.wurlod wrote:Bring NLS_LANG into the project as an environment variable (you do this in the Administrator). Then you can use it in DataStage jobs as a job parameter and set it appropriately for each indivvidual job.

sundar wrote:But the problem now we are facing is for jobs that are directly extracting from one database and loading into another database having different character set in the same job. How to handle different characters (in different stages, in same job) ?
Kindly provide inputs how to resolve the same.

Thanks & Regards
Sundar

Why don't you try with 2 NLS_LANG environment variables, one for each Database?

sumesh.abraham · Post by **sumesh.abraham** » Thu Feb 07, 2008 11:45 am

We included NLS_LANG as User Defined Environment variable and included the same in our jobs

I also face the same issue when I run a job which has any Oracle stage and the warning is

main_program: An NLS character map <ISO8859-1> is specified, but no entry was found in $APT_ORCHHOME/etc/oracle_cs.txt.

I've created NLS_LANG in DS Admin and set it as ISO8859-1. I have added this environment variable in my job's properties. How can I use it now for curbing the warning.

Is it like we need to find the value for NLS_LANG in the Oracle DB and specify the same in the NLS Map of the Oracle stage and add NLS_LANG in the jobs. Any help would be highly appreciated.

Thanks,
Sumesh

manishk · Post by **manishk** » Thu Feb 07, 2008 12:49 pm

I think that Database should also support the same NLS Format.