Reading & Writing 8 bit chars-NLS on DataStage and Oracl

[Ian]

Hi All,
We have an input file that has an 8 bit character in it, essentially used to identify a transaction type (they have used from 0 to 127, and now they are sending us the character equivelants up to 150 or so).

At the moment we are running DataStage 7.1 without NLS support, and our target is a Oracle 8.1.7 database with an NLS_CHARACTERSET of ASCII7. The concern is that we are not going to be able to hold these new characters in the database.

I see that I have two choices available to me.
1. interpret the incoming data item as its numeric equivelant, and when inserting into the oracle table, use the ascii(numeric_equivelant) function.
2. Allow the data to pass through both DataStage and Oracle with the database being loaded as is into the target database.

In situation 1, I believe that I would need to run NLS on DataStage, setting the on DataStage to be the same as the character set of the incoming data for this to work.

In situation 2, I belive that I also need to run NLS on DataStage and the target database. Both of which would need to be running the same character set of the incoming data.

I believe that the option I would prefer is option 1. But at this point, I am getting rather strange results, with my numeric values of the ascii equivelants being in the range of 50000.

Has anyone had any similar experience or thoughts?

Regards,

IanG.

[ArndW]

Ian,

in your case your are still getting valid 1-byte character codes and don't have to worry about multibyte at all. In addition, you are not doing any mapping from one set to another so you do not need the additional overhead of NLS for your ETL process.

Before you go into NLS you need to know what those "unprintable" (on your screen) characters that you are receiving are supposed to represent and get mapped to. Most likely they are to remain as they are, in which case you would take your option (2) but as you are not doing an manipulation, collating or interpretation of this non-basic-ASCII character data you don't need NLS.

I am not sure of your comments re

I believe that the option I would prefer is option 1. But at this point, I am getting rather strange results, with my numeric values of the ascii equivelants being in the range of 50000.

How do you get an ASCII value of 50000? The SEQ(<char>) won't give more than 255 unless NLS is enabled.

[ray.wurlod]

Beware that Char(128) is reserved for the internal representation of NULL in DataStage.
And, yes, if you are getting values over 255 then you have NLS enabled, and what you are seeing is not ASCII, but Unicode code points. However these are not possible with ASCII7.
Therefore, whatever "they" are providing in your source file can not be loaded into Oracle as things stand. You may need to use the ByteVal() function to investigate individual bytes, rather than the Seq() function.

[Ian]

The 50000, odd values were me playing around with an installation of DataStage with NLS enabled to find a solution.

Thanks for the multibyte pointer Ray. Using the non NLS enabled DataStage i think that a solution to our problem is to use a char(seq(VALUE)). This appears to successfully convert MOST of the values. Not to sure if the other ones were valid in the first place, so we are going back to the data suppliers to determine what they were.

Cheers,

Ian.