Not able to View Data. -- NLS Enabled
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
View Data is known to be a bit flaky where NLS is involved. Are the data being processed correctly? Can you view them using other techniques (such as text editor for text files, SQL for database tables, Data Set Management for Data Sets)?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You can read it with any text viewer/editor.
With no commercial relationship I recommend UltraEdit, which can FTP the file to your client machine for viewing.
With no commercial relationship I recommend UltraEdit, which can FTP the file to your client machine for viewing.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
So that means cant we do view data at all ?ray.wurlod wrote:View Data is known to be a bit flaky where NLS is involved.
If yes, then How can I load that data into Oracle using DataStage which is my main goal (Sorry I forget to mention this in Original post
![Sad :(](./images/smilies/icon_sad.gif)
so It will be:
Code: Select all
Seq File Stage ---> Transformer ---> Oracle Stage
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
AMERICAN won't know about the pound sterling symbol. You need to find the appropriate setting, probably one of the ISO8859 ones, that does know about this character.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks for your replay
Thanks for your time
So dosnt it metter what NLS_LANG value has been setup at the database level?ray.wurlod wrote:AMERICAN won't know about the pound sterling symbol.
How can we decide proper character set? Or do we need to do trial and error?ray.wurlod wrote: You need to find the appropriate setting, probably one of the ISO8859 ones, that does know about this character.
Thanks for your time
Of course that matters. You need to find out what characterset your database is using, if it doesn't support what you are trying to load no amount of DataStage NLS magic will make it happen.swades wrote:So dosnt it metter what NLS_LANG value has been setup at the database level?
Your session parameters are not the key here, the database ones are. What is the result of this query?
Code: Select all
select * from nls_database_parameters
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
It is:chulett wrote:What is the result of this query?Not just LANGUAGE and TERRITORY but what does CHARACTERSET say?Code: Select all
select * from nls_database_parameters
I used value of NLS_LANG=AL32UTF8 and set NLS map in Oracle Target Stage = $NLS_LANG but Job is abortingNLS_CHARACTERSET = AL32UTF8
NLS_LANGUAGE = AMERICAN
NLS_TERRITORY = AMERICA
![Sad :(](./images/smilies/icon_sad.gif)
In log it is(Event Type of Log= Info):
Can you please guide me how can I load data correctly ?FATAL ERROR (APT_CharsetConverterRep::initConv()): AL32UTF8 is not a valid codepage.
Thanks
Well, not really the Codepage God that Ray or Arnd are, but the next part of the exercise is this:
What 'codepage' or character set was your data encoded with? This will tell you if a conversion needs to happen between the source and the target or if the data can be loaded directly. So, depending on the answer, some general guidelines:
1) Identical. In this case you need to ensure the character set DataStage is using matches the target database character set. When both 'ends' match, no conversion takes place and the data is loaded 'intact' - i.e. with the encoding from the source. Right or wrong. This is typically what leads to 'garbage' characters in your target, different encodings moved over without conversion and thus misinterpreted in the end.
2) Different. This is where it is critical that your DataStage job be set to the character set of the source data, the database will handle its part automatically. When it sees they are different, it will convert from one to the other during the load. Hopefully correctly, assuming you've gotten the source side set correctly. This too can lead to 'garbage' if done incorrectly.
Bottom line is does your database character set support the pound sterling symbol? Your DBA should be able to help answer that question if you are unsure. Too late for this advice in a sense, but this URL talks about character sets in Oracle and how to choose the right one. And this specifically mentions the AL32UTF8 character set, based on the Unicode UTF8 character set, that you are using and notes that it is a 'universal' character set. It also discusses the same conversion phenomena that I did. It seems that you should be able to accomplish what you need to once you ascertain the source character set.
Then there's the whole topic of viewing the loaded data in a manner that doesn't make it look like it failed. We'll save that for later.
ps. NLS_LANG is more than just the character set. It is a combination of all three elements I asked you to check on for Oracle, so (assuming your source matches your target) set it thus:
And see what happens!
What 'codepage' or character set was your data encoded with? This will tell you if a conversion needs to happen between the source and the target or if the data can be loaded directly. So, depending on the answer, some general guidelines:
1) Identical. In this case you need to ensure the character set DataStage is using matches the target database character set. When both 'ends' match, no conversion takes place and the data is loaded 'intact' - i.e. with the encoding from the source. Right or wrong. This is typically what leads to 'garbage' characters in your target, different encodings moved over without conversion and thus misinterpreted in the end.
2) Different. This is where it is critical that your DataStage job be set to the character set of the source data, the database will handle its part automatically. When it sees they are different, it will convert from one to the other during the load. Hopefully correctly, assuming you've gotten the source side set correctly. This too can lead to 'garbage' if done incorrectly.
Bottom line is does your database character set support the pound sterling symbol? Your DBA should be able to help answer that question if you are unsure. Too late for this advice in a sense, but this URL talks about character sets in Oracle and how to choose the right one. And this specifically mentions the AL32UTF8 character set, based on the Unicode UTF8 character set, that you are using and notes that it is a 'universal' character set. It also discusses the same conversion phenomena that I did. It seems that you should be able to accomplish what you need to once you ascertain the source character set.
Then there's the whole topic of viewing the loaded data in a manner that doesn't make it look like it failed. We'll save that for later.
![Wink :wink:](./images/smilies/icon_wink.gif)
ps. NLS_LANG is more than just the character set. It is a combination of all three elements I asked you to check on for Oracle, so (assuming your source matches your target) set it thus:
Code: Select all
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Funny, but in thinking about these pearls of wisdom I wrote up earlier, I realized there really aren't two different paths to take, only one. And that is knowing the encoding of your source data and making sure the DataStage environment for the loading job reflects that. Both NLS_LANG and LC_CTYPE can easily be set up as User Defined Environment Variables set to $ENV in the Administrator and then added (and thus overwritten) in any job that needs to have them set differently than the default.
If you are unsure, ask your vendor / supplier / file pusher.
If you are unsure, ask your vendor / supplier / file pusher.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers