NLS question re: Spanish characters

chulett · Post by **chulett** » Mon Nov 28, 2005 11:29 am

Ok, I'll be the first to admit I'm an NLS virgin - I understand the concept but haven't had a need yet to work outside of the default, non-NLS-enabled character set. I'm not even sure how to check what the character set is when NLS is not enabled.

So, I have a question...

Both source and target databases are Oracle using the WE8IOS8859P1 character set. In one table, there are a handful of records with a field to track the Spanish version of a certain piece of data. They are correct in the source database (of course) but after coming into the target via DataStage, the Spanish characters like the upside-down question mark or the letters with the tilde over them are converting to question marks as they are unmappable at the moment.

Is there a DataStage solution to this that would not require us to enable NLS across our entire project for these handful of rows in one table? I know we can go outside the tool and have people working on that, but was wondering if (perhaps) something could still be done to handle this without enabling NLS.

ArndW · Post by **ArndW** » Mon Nov 28, 2005 11:41 am

Craig,

you don't need to enable NLS for this. Just make sure your conversion in DS is none and that you have the ENVironment settings for oracle correct. The input conversion to/from Unicode should map 1:1 and not convert characters; especially since the upside-down-question-mark and other Spanish characters are part of the default LATIN character set.

If you output the strings to a sequential file after reading them are they already incorrect?

chulett · Post by **chulett** » Mon Nov 28, 2005 12:12 pm

ArndW wrote:If you output the strings to a sequential file after reading them are they already incorrect?

Good to hear. Not sure on this, let me check...

Sorry, but I'll need some clarification on what you mean by 'conversion in DS is none' and what the 'correct ENV settings for Oracle' would be.

chulett · Post by **chulett** » Mon Nov 28, 2005 12:31 pm

Yes, just the act of doing a 'View Data' from the source OCI stage shows the data 'incorrectly'. So, yes, in the sequential file they are wrong as well.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 30, 2005 12:42 am

"Conversion in DS is none" really means that, if you have NLS enabled, the mapping within DataStage should be set to the pseudo-map NONE.

If you don't have NLS enabled, the point is moot.

ArndW · Post by **ArndW** » Wed Nov 30, 2005 2:39 am

Craig,

I lost this thread in the crush, sorry. I'm not at an Oracle site, but I thin the environment settings for ORA_NLS and NLS_LANG are the ones that influence a conversion. Also, what ASCII value does that upside-down-question-mark get converted to in the sequential file (the view data will always mess up values so it is useless for debugging)?

chulett · Post by **chulett** » Thu Dec 01, 2005 10:52 pm

I had a chance to nail down my DBA for a moment today and he concurs with your assertation, Arnd. Specifically, he suggests I set this:

Code: Select all

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

I've made the tweak to our dsenv file but I'll be damned if I can find a quiet moment that I can bounce the Server. Got a ton of who-ha going on right now and it seems like we've got jobs running on our dev/test server 24x7.

Hopefully, I'll get a chance over the weekend to give this a shot!

chulett · Post by **chulett** » Mon Dec 19, 2005 1:53 am

PS. This did work, by the way. :D

It does help tremendously, however, if when you set NLS_LANG in your dsenv file that you also export it properly. Exporting NSL_LANG made it seem like it wasn't going to work until I finally noticed the typo. Duh.

srinagesh · Post by **srinagesh** » Mon Dec 19, 2005 9:58 am

Hi !

Sorry to hijack the thread.. But its a related query..

I think changes to the dsenv file will apply to all the projects.
Our datastage server hosts a number of projects, and I would like to apply these settings only to specific project.

Can this be done ?

Regards
Nagesh

chulett · Post by **chulett** » Tue Dec 20, 2005 10:48 am

The solution is pretty straight forward - put an NLS_LANG entry in your dsenv file that matches your source / target systems and you should be fine.

raoraghunandan · Post by **raoraghunandan** » Wed Dec 21, 2005 5:09 am

chulett wrote:The solution is pretty straight forward - put an NLS_LANG entry in your dsenv file that matches your source / target systems and you should be fine.

But as mentioned by someone in the post, wouldn't changing the dsenv file affect all projects that run on the DataStage Server. Instead cannot this be a user-defined variable ?

Thanks,
Raghu

chulett · Post by **chulett** » Wed Dec 21, 2005 8:43 am

Yes, it affects all projects on the server when done in the dsenv. For us, that's exactly what we needed. I have no idea how to set something like that different from project to project, perhaps someone else does...

ray.wurlod · Post by **ray.wurlod** » Wed Dec 21, 2005 1:46 pm

It's an environment variable, for goodness' sake! You can change it after dsenv has been executed, for example using an Execute Command activity in your job sequence. If you're really brave you could add to the LOGIN paragraph in the project - but be prepared to reinstate this after an upgrade, which replaces VOC entries.

chulett · Post by **chulett** » Wed Dec 21, 2005 1:49 pm

For goodness' sake!