NLS settings

JeroenDmt · Post by **JeroenDmt** » Mon Jun 15, 2009 6:25 am

I am working on the migration from DataStage 7.0 to DataStage 8.0
I am having some NLS-related problems. Now IBM is suggesting to change the NLS_LANG setting for all projects, but I don't know what the impact of that is, and also I don't know what is the correct value for the NLS parameters according to the theory. I know how we have been doing it here, but nobody knows exactly how to use these settings and if the correct results are a coincidence or because we are using these settings. They do give the correct results in DataStage 7.0, but give error messages like 'OCI has fetched truncated data' in DataStage 8.0 for the same jobs/ database/ sequential files.

We are using unix (NLS ISO8859-1 on the server as far as I know; don't know how to check) and Oracle database (NLS_CHARACTERSET=AL32UTF8 on the database server).

Now there are several places to influence the NLS-behavior. Our current settings are:
- NLS_LANG (dsenv) is set to American_America.AL32UTF8 (since the database server is set to AL32UTF8, not sure if that is the correct reasoning).
- Project-default NLS setting=None (no reason, simply the way it's always been)
- stage NLS setting: None for Oracle to Oracle jobs
- stage NLS setting: Sequential File ISO8859-1 to Oracle OCI UTF8 for Sequential file to Oracle jobs (both because that is the setting for the corresponding server; again not sure if that is the correct reasoning).
This has been giving us correct results in DataStage 7.

Now IBM is suggesting changing the NLS_LANG to American_America.WE8ISO8859P1. That does indeed resolve error messages like 'OCI has fetched truncated data' and other strange behavior that I have been getting in DataStage 8. It introduces error messages like "ORA-12899: value too large for column" in sequential file to oracle jobs, which again disappear by changing the stage NLS settings to "Project default (None)".

This raises the question what are the correct settings for these NLS parameters.
I have tried to find out about this on the documentation, but cannot seem to find any answer to this there.

Please advise.

ArndW · Post by **ArndW** » Mon Jun 15, 2009 6:30 am

I would stick with the IBM recommendation and then ensure that your Oracle DDL specifies character lengths in characters, not in bytes, i.e. "VarChar(32 Char)" instead of "VarChar(32 Byte)" or by specifying "alter {session/system} set nls_length_semantics=char;"

JeroenDmt · Post by **JeroenDmt** » Mon Jun 15, 2009 6:34 am

I expect we indeed should stick with the IBM recommendation for NLS_LANG. But how about the NLS settings for the stages? What settings should we use for those?

And do you or anyone have a pointer to some kind of documentation to read more about this? I feel like I'm running around in the dark and as long as I'm not bumping into something, I keep running around in the same circle, but I have no idea if I'm following the best route or not.

(Oracle DDL does specify character lengths in characters)

JeroenDmt · Post by **JeroenDmt** » Tue Jul 07, 2009 12:09 am

We've worked on this with IBM resulting in an a patch for the Oracle OCI stage.

PATCH NAME:patch_JR33405

COMPONENT: Server

PROBLEM REPORTED/Implementation Details:
Oracle OCI Stage doesn't extract CHAR datatype properly
when NLS_LANG is set to American_America.AL32UTF8

So apparently our settings were correct, but there was a bug in DataStage.