Problem with Charachter handling in Dataset

ArndW · Post by **ArndW** » Mon Apr 28, 2008 4:23 am

Are you using NLS? What is the Oracle character set setting and what are you using in DataStage?

Gokul · Post by **Gokul** » Mon Apr 28, 2008 6:16 am

IN oracle it is set to 'AMERICAN' . Since I have don;t have admin right in datastage, I am not able to view it. But it must be different then in oracle, as I am getting this behaviour

ArndW · Post by **ArndW** » Mon Apr 28, 2008 6:17 am

Try output to a PEEK stage - is it correct there?

ArndW · Post by **ArndW** » Mon Apr 28, 2008 6:58 am

Try putting in a transform with the output of "CHAR(In.TimeStampStringColumn[16,1])" to see if it really is a "-"

Gokul · Post by **Gokul** » Mon Apr 28, 2008 7:24 am

It is not '-'. Its hex value is 96. Also I have some value in column having '-' which are processed as it is, without any conversion.

My query is whether channging the NLS of Datastage to 'AMERICAN' will solve the problem and also will it afect other normal running jobs .

ArndW · Post by **ArndW** » Mon Apr 28, 2008 8:08 am

No, nothing like that will have DS automatically convert an input Hex Value of 96 to '-'; this is not an NLS conversion. You can use the

Code: Select all

CONVERT(CHAR(150),'-',In.Column)

ray.wurlod · Post by **ray.wurlod** » Mon Apr 28, 2008 4:34 pm

Is this the em-dash or en-dash character? Or is it some other form of character? Arnd's solution is a good one if the right answer is to convert to hyphen. But what if it isn't? What if the users actually need Char(150) in the result?

The fact that View Data can not handle the character should be immaterial. DataStage can happily handle Char(150) and move it through an ETL process. If that's what's needed, that's what you should do.

Gokul · Post by **Gokul** » Tue Apr 29, 2008 1:32 am

The need of my project was to replicate the changed data from one database to other.

I am fetching the delta from Oracle source database and storing it in a dataset in one job. In other job, the dataset is loaded in the target database.

As a result, I don't want any conversion of the data in between

ArndW · Post by **ArndW** » Tue Apr 29, 2008 1:40 am

As mentioned before, DataStage will not convert data unless either explicitly told to do so or if an implicit NLS conversion is taking place. As you don't have NLS installed then you should just pass the data through without any conversions.

Gokul · Post by **Gokul** » Tue Apr 29, 2008 6:29 am

ArndW,
In fact in my job I am not doing any conversions/transformation.
The data from oracle stage is dumped directly into Dataset. Within the Dataset i am see the char(150) converted to ?.

Even when I peek the values from oracle stage, they are implictly converted

ArndW · Post by **ArndW** » Tue Apr 29, 2008 6:54 am

Ignore the PEEK output. If you copy the data from Oracle through DataStage to Oracle you will have an identical string.