Difference in ORAOCI read result between Oracle 9i and 11g

Hkonishi · Post by **Hkonishi** » Fri Jul 17, 2009 2:41 am

Hello,

As I'm running some data comparison check in ETL job results between Oracle 9i database and Oracle 11g database, I found there are significant difference between results in data fetched from those 2 versions of Oracle database using ORAOCI8 and ORAOCI9 stage.

For example, there are 2 tables whose structure is configured exactly the same on both Oracle 9i database and Oracle 11g database. Let's say table name is S_ORG_EXT_XM.
There is a column named as ATTRIB_01 whose SQL Type and Length is (1) on both database. This column only contains 'Y' or 'N' as a flag.

When I look at the data itself using SQLPlus or SQLNavigator, the column only contains 1 byte of CHAR data such as 'Y' or 'N correctly'.
However, I found that it becomes 'Y__' or 'N__' after it was fetched from Oracle 9i database using ORAOCI stage (2 bytes of spaces were added after 'Y' or 'N').
In addition, when we tried to fetch the same data from Oracle 11g database, ORAOCI stage displays the data as 'Y___' or 'N___' (3 bytes of spaces were added), even though the ORAOCI stages indicating the culumn ATTRIB_01 is CHAR (1) in its column definition.

I've checked both ORAOCI8 and ORAOCI9 stage and confirmed that the same case is happening.

If someone could advise the reason why it happens or any resolution available, very much appreciated.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 17, 2009 3:04 am

Few basic questions

1.) What is the meta-data for the columns in DataStage?
2.) Are they extracted using same job or different ?
3.) Are you fetching directly from the table or from some view over it ?
4.) Are you viewing at the result after some transformation or view-data from Oracle stage ?

chulett · Post by **chulett** » Fri Jul 17, 2009 6:24 am

I've seen this and from what I recall, it's an Oracle bug. What exact Oracle client version do you have installed on your DataStage server? Also note that 11g is not officially supported.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 20, 2009 4:26 pm

Since your Oracle is set up for Japanese, it uses three bytes per character for storage. This the source of the trebling. It is not a bug.

chulett · Post by **chulett** » Mon Jul 20, 2009 4:50 pm

Well, then... I've seen something like this and it was a bug that getting a newer version of the Oracle client solved. Maybe not exactly this.

ray.wurlod · Post by **ray.wurlod** » Mon Jul 20, 2009 5:07 pm

It can also be affected by your CREATE TABLE statement. For example you can define a column explicitly as VARCHAR2(30 BYTES) - which would store 10 multibyte characters - or as VARCHAR2(10 CHAR). There is a global setting to provide the default meaning but I can't recall at the moment what it is.

etldwh_techie2050 · Post by **etldwh_techie2050** » Mon Jul 20, 2009 6:32 pm

Hi HKonishi, Have you tried datastage NLS settings? Is you local in Oracle 9i & 11g is in Japanese and if you are datastage NLS setting local is in English?

Quick fix to the spacing issue in Char datatype would be using datastage Trim() function.

Hkonishi · Post by **Hkonishi** » Mon Jul 20, 2009 11:41 pm

Please let me list my answers for each question given.

Q1) Meta-data for the colulmn in the DataStage job?
A1) I have hundreds of columns in several DataStage projects which were configured as Key=No, SQL type=CHAR, Length=1, Scale=blank, Nullable=Yes, Display=10, Data element=blank, Description=blank.

Q2) The same job used to extract data from 9i and 11g database?
A2) Yes. Only the frontend difference currently seen is the targeted source database version which is Oracle 9i and 11g.

Q3) Fetching directly?
A3) Yes, fetching directly from source databases using DataStage Server job, some are with ORAOCI8 stage and the others are with ORAOCI9 stage, no OCI10.

Q4) Oracle is set up for Japanese?
A4) Yes, but NLS for Oracle 9i database is set as NLS_LANG=American_America.UTF8 and Oracle 11g database is set as NLS_LANG=American_America.AL16UTF16 as UTF8 is no longer a supported characterset for Oracle 11g.
I found an article which commented the similiar type of issue in Quality Stage.
http://www-01.ibm.com/support/docview.w ... 9websphere

Q5) Oracle client version on DataStage server?
A5) No Oracle client was installed on to our DS server at this stage. We are now planning to install 10g client by the end of this year.

Q6) DataStage NLS settings.
A6) Yes, I've tried major NLS choices and combinations in DataStage Administrator/Job Properties/Job itself but had no luck.

Q7) NLS Locale in database and DataStage.
A7) This is set to OFF. This is because we are using common DataStage server shared globally within company.

And yes, adding Trim() at Transformer stage or changing CHAR(1) into VarChar at the job may also be an option, however, at this stage, we would like to see if there are any alternative solutions which can reduce our workload or not, due to the large number of jobs and columns which are currently configured to extract data as CHAR(1).

Thank you for all your advice.

chulett · Post by **chulett** » Tue Jul 21, 2009 5:28 am

Hkonishi wrote:Q5) Oracle client version on DataStage server?
A5) No Oracle client was installed on to our DS server at this stage. We are now planning to install 10g client by the end of this year.

Not possible. The OCI stages do not function without an Oracle client installed on the DataStage server. FYI.

Hkonishi · Post by **Hkonishi** » Wed Jul 22, 2009 1:38 am

Okay, please wait I get back again as I'm now asking the person who advised to check if any Oracle client installed or not again.

Hkonishi · Post by **Hkonishi** » Wed Jul 22, 2009 1:55 am

Hello chulett,

You're right. Please let me correct previous statement as follows.

Q5) Oracle client version on DataStage server?
A5) Oracle 9i client is installed on to our DS server at this stage. We are now upgrade it to 10g client by the end of this year.

Thanks!

Hkonishi · Post by **Hkonishi** » Mon Jul 27, 2009 8:24 pm

Hello,

Can someone has alternative solution on this issue?