Unable to decide which NLS should be used where??

saadmirza · Post by **saadmirza** » Wed Jun 15, 2005 2:09 am

Hi All,
I have a DS job whose output goes as input to QS job...

DS Job:
OCI---->TX----->Sequential File(Fix length)

My Oracle table contains special german and scandnavian characters...If i do a select sql i am able to see them in Oracle which I think rules out that Oracle is not compliant...My NLS for OCI is UTF 8 and my NLS for Seq File is UTF 8..When I run the job i dont see desired output...when I turn my OCI NLS to ISO8859-1 and NLS of Seq file also to ISO8859-1 evrthing sems ok...but this fixed lentgh seq file is input to Quality Stage...and there it creates a problem since QS only takes UTF 8 as default for Windows system...How do I reslve this issue...

Please suggest..

Thanks,
Saad Mirza

ArndW · Post by **ArndW** » Wed Jun 15, 2005 3:30 am

saadmirza,

first of all, in this case I would say not to use NLS at all in DataStage; let DataStage just pass through the values unchanged and unmapped.

Secondly, you would need to look at what happens to a specific character; just saying that "i don't see the desired output" isn't really going to help in analyzing what is going wrong.

Take a column with just one character, use a German-only character such as the "Sz" (looks like a capital B, represents a double-s) or the A-umlaut and see what your text file contains. Make sure you use an editor that can display German characters correctly, better yet use an editor that will show you the numeric character values so you can be 100% certain of what you've received. You can also add an output from the DataStage job to the sequential file and use the SEQ(<character>) function to get the ASCII numerica value that DataStage sees.

ArndW · Post by **ArndW** » Wed Jun 15, 2005 6:50 am

Saadmirza,

add in a column to your output which is derived from SEQ(Y=<YourString[3,1]) to get the ASCII value of the o-umlaut coming into to DataStage. The square box is typical for a character that cannot be displayed in a given editor. Which method did you use to display this string?

saadmirza · Post by **saadmirza** » Wed Jun 15, 2005 7:54 am

Hi Wolfgang,
Ans 1)
Yes I am able to read the special chracters using SQL select statements

Ans2)
I created a job using Input OCI and output Sqequential...my output is fixed lentgh format file..I dont see special characetrs in my output sequential file.I open the file in Notepad editor.I open other files in notpead, I am able to see german chars means Editor is not an issue...

Please suggest.
Saad

WoMaWil · Post by **WoMaWil** » Wed Jun 15, 2005 8:09 am

Hi Saad,

if you see the german characters in the output the DataStage processing is not the problem.

Write some german charactes in you target file with SQL means and check if you can read them with SQL means.

Wolfgang

saadmirza · Post by **saadmirza** » Wed Jun 15, 2005 8:13 am

Hi Wolfgang,
I do not see German characters in my output of Datastage...

saad

ArndW · Post by **ArndW** » Wed Jun 15, 2005 8:23 am

Saad,

so what number character value is DataStage converting an umlaut character to?

ArndW · Post by **ArndW** » Wed Jun 15, 2005 9:29 am

saadmirza,

in order for us to figure out what is happening we need to know what the output value is -> that way we can backtrack to see the conversion or mapping that was used and, using that information, analyze what has occurred.

Please check and fix your SEQ statement - it does only one character, not a whole string. You need to specify it as I mentioned earlier.

ArndW · Post by **ArndW** » Wed Jun 15, 2005 9:30 am

saadmirza,

in order for us to figure out what is happening we need to know what the output value is -> that way we can backtrack to see the conversion or mapping that was used and, using that information, analyze what has occurred.

Please check and fix your SEQ statement - it does only one character, not a whole string. You need to specify it as I mentioned earlier.

ArndW · Post by **ArndW** » Wed Jun 15, 2005 10:43 am

Please add a LEN(link.Firstname) to the output columns so you are 100% sure that it only contains one character.

saadmirza · Post by **saadmirza** » Wed Jun 15, 2005 11:37 am

Yes ..It is showing me length as 1..

Saad

ArndW · Post by **ArndW** » Thu Jun 16, 2005 1:43 am

saadmirza,

I cannot get the SEQ(<1-char-string>) to output a blank, so I think that something is going wrong with your output. Have you tried it with another, non-German, character such as a space and gotten the expected value of 32 back?

ArndW · Post by **ArndW** » Thu Jun 16, 2005 3:02 am

Saad,

you are still doing something wrong. The "1" you are seeing is the LENgth of the string, not the SEQ value of that character, since that would be a SOH (start-of-heading).

I am sorry, but this step is the easiest part of the process and should take no more than a minute to write, compile & test. You are adding 2 columns to your output text file, one showing the SEQ() value of a character and the other the length. If you test this - not with your German character but with a space or the letter "a" you should see the output of 32 and 97 respectively. Once you have the known test case working correctly, you can try it with the {expected} umlaut and see if you {or someone in this forum} can find a correlation between your expected value and your actual value.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 16, 2005 3:08 am

Can you try the same test using the UNISEQ function rather than SEQ ?

The UNISEQ function returns the Unicode code point of the character that is its argument. This may give some insight into the block in which your strange character belongs.

ArndW · Post by **ArndW** » Thu Jun 16, 2005 7:41 am

Saad,

according to the standard ASCII tables the capital-O-umlaut is supposed to be 214. This means that DataStage is writing this value to your output file correctly, unless you have specified a conversion on the output stage. Is this correct?