DSXchange

hi,

we have datatype number in oracle database, but when we try to import the data in datastage it is converting decimal datatype. is it possible to convert as int instead of decmail with out manua intervention.because we are using runtime column propagation for around 100 Tables in same job.

I'm assuming this is an unbound number in Oracle, yes? One with no precision specified. Have you actually tried using the metadata as imported or just assuming the worst? It should work fine, I'll wager.

hi Chulette,

I have tried importing metadat, datatype changed to Decimal but we need integer

Thanks

I'm disputing your "need" for integer and asked if you'd actually tried to use the DECIMAL metadata as is in a job. If so, what was the issue / problem you found?

with RCP we are reading almost 500 tables and creating files. our target teams identified some of the key column displaying in decimal. they don't want in decimal.....

The "creating files" part would have been good to mention right up front.

By default you should be getting the fractional part of the values truncated if the Oracle field is define as number(p,s) and the DS metadata for same field is define as integer ( it truncates the fractional part of the decimal value), but I guess that due to run time column propagation you are not defining any

If you like you could try to get the behavior that predate IIS9.1 to see if that help. Before IIS9.1 the Oracle connector if you read Oracle number define with (p,s) to a DS decimal without scale or to integer then the decimal values will be round to integer values (SQLT_CHR), which looks like is what you are looking for, correct?

i.e 123.000 --> 123
123.450 --> 123
123.540 --> 124

Add below user environment variable to your project set to empty, add it to your DS job and set to TRUE

CC_ORA_BIND_DECIMAL_AS_CHAR

Let us know how it goes

Not sure if its good to continue on this post, but this being the only post that talks about the problem of interpreting number as decimal in rcp, i thought of continuing on the same.

Coming back to the subject, we have the exact same requirement of not to send the integer as decimals, whereas the oracle connecter is interpreting the number as decimal format which is correct because the number format defined in oracle table has a seperator(NUMBER(7,2) or NUMBER(5,0), But this column never gets a decimal. Ideally we have to change the column data type, but it would be a nightmare to change it in all the tables and corresponding tests etc...

Hence thought of handling them at datastage, and the environment variable "CC_ORA_BIND_DECIMAL_AS_CHAR" proposed here was interesting, but i do not find them in 11.5 https://www.ibm.com/support/knowledgece ... arora.html

Has anyone overcame this issue? Any pointers would be of great help.

Thanks