Last digit gets truncated while loading into Oracle ODBC Stg

poorna_76 · Post by **poorna_76** » Fri Mar 30, 2007 3:39 pm

Hi,

Our current data warehouse Datastage applications are using MS SQL Server 2000 as backend database and we are in the process of migrating
to Oracle 10g. We found an issue while System Testing.

We have jobs that are reading from Complex flat file and loading it into ORACLE ODBC stage.

For a particular AMOUNT field (SQL Type DECIMAL , Length 11, Scale 2
and Display 13) source data value is 123456789.12 and while loading data
into Oracle Database using ODBC stage the last digit is getting truncated
and data is getting inserted with value 123456789.1

When we replace Target ODBC stage with DRDBMS stages (DBMS Type
ORACLE/ODBC) or Oracle OCI stage, it inserts 123456789.12 properly.

Are there any datastage functions or options available to avoid this data
truncation in Target ODBC stage?

For us to replace Target ODBC with Oracle OCI is like redoing all of our
jobs since we found this issue while System testing.

Thanks

DeepakCorning · Post by **DeepakCorning** » Fri Mar 30, 2007 3:59 pm

Did you try increasing the scale and test it?

parag_pk · Post by **parag_pk** » Sat Mar 31, 2007 7:09 am

DeepakCorning wrote:Did you try increasing the scale and test it?

Yes, We increased width to 13,2 display 15. Data got inserted properly.

Thanks

ray.wurlod · Post by **ray.wurlod** » Sat Mar 31, 2007 10:45 am

Could be time to mark thread as resolved, then.

parag_pk · Post by **parag_pk** » Sun Apr 01, 2007 7:13 am

ray.wurlod wrote:Could be time to mark thread as resolved, then.

We tried to insert data with other stages like Ora OCI and DRDBMS
by keeping same width 11,2 and display 13 data is getting inserted properly.

but our issue is, when we keep same width and scale (11,2 and display 13) in ODBC Stage. Its NOT working and most of our jobs are having ODBC stage.

Thanks

chulett · Post by **chulett** » Sun Apr 01, 2007 7:51 am

And?

You've got your answer - you will need to adjust your metadata for use in the ODBC stage, it seems. You can thank Bill Gates for that. Only question in my mind is: does it make a difference if you use the Oracle 'wire' drivers over the 'non-wire' ones? Which have you tried?

Rhetorical question, but why would you use ODBC when you have a 'native' interface available, in this case OCI? To me, ODBC is the 'interface of last resort'...

ray.wurlod · Post by **ray.wurlod** » Sun Apr 01, 2007 9:39 am

But easier to change databases subsequently when some smooth-talking sales person convinces management that dBase IV is the only database you'll ever need and it's cheap as well.

parag_pk · Post by **parag_pk** » Mon Apr 02, 2007 6:47 am

ray.wurlod wrote:But easier to change databases subsequently when some smooth-talking sales person convinces management that dBase IV is the only database you'll ever need and it's cheap as well.

Update - We have been notified by IBM that there is patch available for this issue.

Thanks

chulett · Post by **chulett** » Mon Apr 02, 2007 6:51 am

Good to know. If you can, please post the ECASE number for this patch, it will help others with the problem get the solution as easily as possible.