NLS settings
Posted: 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.
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.