DSXchange

Hi -
I've spent the last week pouring over post after post regarding NLS_LANG settings, UTF8, etc. I know in one post I saw it stated that,

you can change the project default through the Administrator Client

But, for the life of me, when I go to the administrator client, I don't see where it will allow me to even view what our default setting is...
What I need to know is how can I tell what our default setting is and, if necessary, how do I change it.
Please advise...

Thanks

In the Admin clients, you have ADD, REMOVE etc tabs below that you have NLS tab there you can set the value.

In a "non-NLS enabled" environment, there's no tab as you don't really have a default setting AFAIK. That or it is effectively set to "none". Nothing stopping you from adding a user defined environment variable setting $NLS_LANG to UTF8 if you so desire and it would automatically take effect in all jobs of the project.

Not sure I would take that route, however. I prefer to just set it in the jobs that need it. One way to do that would be to still add the Project level value via the Administrator but set its value to "$ENV", which sets it to the current environment's value - including nothing. Then add it to a job and 'override' it there to UTF8.

Thanks Craig.
I tried your suggestion as follows:
1. went to Administrator and set up NLS_LANG as a user defined environment variable with the default value = $ENV
2. in my job parameters I set $NLS_LANG to (string value) UTF8

But my job failed with this error message:

ORA-12705: invalid or unknown NLS parameter value specified

Did I set this up wrong? In the "value" section of the user defined variable in the administrator - should I put the default value as $ENV or should I have simply put ENV?

Thanks much!!

Thanks Craig.
I tried your suggestion as follows:
1. went to Administrator and set up NLS_LANG as a user defined environment variable with the default value = $ENV
2. in my job parameters I set $NLS_LANG to (string value) UTF8

But my job failed with this error message:

ORA-12705: invalid or unknown NLS parameter value specified

Did I set this up wrong? In the "value" section of the user defined variable in the administrator - should I put the default value as $ENV or should I have simply put ENV?

Thanks much!!

$ENV is correct, what's not correct is your $NLS_LANG value. "UTF8" doesn't stand on its own, try "AMERICAN_AMERICA.UTF8" instead. Note that you may also need to set "LC_CTYPE" for this to work, I use "C.utf8" there.

Thanks Again Craig!
I replaced "UTF8" with "AMERICAN_AMERICA.UTF8" and that appears to have done the trick.

For the record - what is LC_CTYPE? And do I need to set this in the administrator as a user defined env variable? If yes, then do I set the default value (in admin) to "C.utf8" or do I set it to something else and then set the value in my job to "C.utf8"?

Thanks

It's part of the "locale" settings in UNIX, something I can't really explain but have run afoul of. Check here for an example or google around, which is what I did back in the day. I found I needed to add it to make UTF8 "work work" - if you don't have any issues, don't worry about setting it.

And yes, rather than setting it to a value in the Administrator which would immediately add that to all jobs, I'd stick with the "set to $ENV and override where needed" trick.

I think I'll go with the

if you don't have any issues, don't worry about setting it

idea...but thanks.

Now - here's a continuation of my dilemma. I am sourcing my data from Oracle - which is also set to UTF-8 - so the sourcing is working much better now - thank you. HOWEVER, my target is a table in a SQL Server database - which uses the funky UCS-2 character set which is evidently closely related to UTF-16. So, how do I convert my UTF-8 to be compatible with SQL Server UCS-2 when I load the data? I've tried simply changing my $NLS_LANG value from AMERICAN_AMERICA.UTF8 to AMERICAN_AMERICA.UTF16 which, I'm sure you know, doesn't work.

Thanks much!!!

I think I'll go with the

if you don't have any issues, don't worry about setting it

idea...but thanks.

Now - here's a continuation of my dilemma. I am sourcing my data from Oracle - which is also set to UTF-8 - so the sourcing is working much better now - thank you. HOWEVER, my target is a table in a SQL Server database - which uses the funky UCS-2 character set which is evidently closely related to UTF-16. So, how do I convert my UTF-8 to be compatible with SQL Server UCS-2 when I load the data? I've tried simply changing my $NLS_LANG value from AMERICAN_AMERICA.UTF8 to AMERICAN_AMERICA.UTF16 which, I'm sure you know, doesn't work.

Thanks much!!!

You need to find out what the actual NLS_LANG setting is for your target and use that in the job that writes to it. And if you really do need to use a different NLS_LANG setting, source v. target, then you'll need to split your job in two if you haven't done so already.

Thanks for sticking with me Craig!
Yep - I"m with you. Here is my current job designs: I understand that UCS-2 is a "subset" of (or at least very compatible with) UTF-16. BUT - keep in mind that I am on a NON-NLS enabled system. So to what do I set my $NLS_LANG value for the job attempting to load the SQL server database?

Can't thank you enough for your help!!!

I'm afraid I don't really have an answer for that. Can you talk with the SQL Server DBA and find out what the equivalent of their NLS_LANG value is set to?

Just a note that I'm going to mark this topic "resolved"....

At least I now know how to make my non-nls enabled datastage read UTF8 data from Oracle. NLS_LANGUAGE = American_America.UFT8

We never did figure out how to push this data - with valid characters in place of the unreadable ones - into the MSS databases. "We" being myself and our MSS DBA's.

Thanks for all your help!

Just a note that I'm going to mark this topic "resolved"....

At least I now know how to make my non-nls enabled datastage read UTF8 data from Oracle. NLS_LANGUAGE = American_America.UFT8

We never did figure out how to push this data - with valid characters in place of the unreadable ones - into the MSS databases. "We" being myself and our MSS DBA's.

Thanks for all your help!