I have an Oracle database with NLS_LANG = American.America.WE8ISO8859P1 and the same value for NLS_LANG in our dsenv file. In this database there is a table with a VARCHAR2(10) column which contains superscripts as in cubic feet (ft3). (The 3 should be raised). The hex value for the 3 here is x'B3'. Copying this data to a different table in the same instance of the database using a DataStage server job (we did not specify the NLS option when doing the DataStage install) results in all superscripts (and other similar characters) being replaced by a '?'. This does not happen if the copy is accomplished via SQL*Plus using an Insert/Select statement. Results of both ways were observed using SQL selects as I know that View Data does not always show the right thing.
Thus, it is obvious that it is DataStage which is causing this. Others have said (in the DSXchange forum) that they can handle such things as European characters like umlauts (x'DC' for example) without using NLS in DataStage. How can we do this with superscripts?
Thanks for any help.
Larry
Trouble with special characters - like super-scripts
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
0xB3 is Char(179) which is not a printable character in ASCII (which is what DataStage uses if NLS is not enabled). It will handle the character OK, but will have difficulty displaying it. It will almost certainly not be able to display it as superscript given that you're on a UNIX server; UNIX-based character sets tend not to know about superscript and subscript.
I suspect this will be true even if NLS is installed and enabled.
I suspect this will be true even if NLS is installed and enabled.
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.
Ray,
I understand that DataStage will not be able to display this character properly, but it also does not seem to handle it correctly. That is, after the job runs, the data in the target table does not contain the superscript - it has been replaced by a '?'. This can be seen be doing an SQL query (I've done it in both Toad and SQL*Plus). Using the same query against both the source table and the target table (changing the from clause only) shows superscripts in the source and '?' in the target.
Larry
I understand that DataStage will not be able to display this character properly, but it also does not seem to handle it correctly. That is, after the job runs, the data in the target table does not contain the superscript - it has been replaced by a '?'. This can be seen be doing an SQL query (I've done it in both Toad and SQL*Plus). Using the same query against both the source table and the target table (changing the from clause only) shows superscripts in the source and '?' in the target.
Larry
Hi Larry,
All the unprintable characters are replaced by "?" only for display purpose. But actually theyare not equal to "?". Say the string you got is "27ft?" where the "?" is the replacment for superscript. Try equating it with "27ft?". The two strings wont match.
There is one more way to check this. Try Doing OConv(yourString,"MCP"). If the "?" in yourString is replaced by "." then that means that the special character is still there but since it is not in ASCII and hence cant be displayed, so it is being displayed as "?"
Mrinal
All the unprintable characters are replaced by "?" only for display purpose. But actually theyare not equal to "?". Say the string you got is "27ft?" where the "?" is the replacment for superscript. Try equating it with "27ft?". The two strings wont match.
There is one more way to check this. Try Doing OConv(yourString,"MCP"). If the "?" in yourString is replaced by "." then that means that the special character is still there but since it is not in ASCII and hence cant be displayed, so it is being displayed as "?"
Mrinal
Mrinal,
I have done what you requested, but did not get what you expected. The "?" remained "?", they did not get converted to ".".
What that means is what I said before - it is not simply that they are only being displayed as "?", DataStage is actually converting the special characters to "?".
Any more suggestions anybody?
Larry
I have done what you requested, but did not get what you expected. The "?" remained "?", they did not get converted to ".".
What that means is what I said before - it is not simply that they are only being displayed as "?", DataStage is actually converting the special characters to "?".
Any more suggestions anybody?
Larry
OK, now is the time for owning up to being foolish!
This problem was due to a few people here not being able to see what they were seeing. NLS_LANG was not being set in the dsenv simiply because the export NLS_LANG statement was missing. Adding the export fixed the problem.
We have all scheduled eye exams ASAP. Scary thing is that this has been this way since Aug., 2002!!
Thanks all.
Larry
This problem was due to a few people here not being able to see what they were seeing. NLS_LANG was not being set in the dsenv simiply because the export NLS_LANG statement was missing. Adding the export fixed the problem.
We have all scheduled eye exams ASAP. Scary thing is that this has been this way since Aug., 2002!!
Thanks all.
Larry
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This can be a trap, because dsenv runs in Bourne shell (check out line 1). Anyone used to automatically exported environment variables (like in some of the newer, "cleverer" shells) is likely to forget to include the export commands, even though there are already some there!
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.