Number datatype converting to decimal datatype while import

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
mallikharjuna
Participant
Posts: 81
Joined: Thu Nov 30, 2006 7:46 am
Location: india

Number datatype converting to decimal datatype while import

Post by mallikharjuna »

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.
MALLI
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
mallikharjuna
Participant
Posts: 81
Joined: Thu Nov 30, 2006 7:46 am
Location: india

Post by mallikharjuna »

hi Chulette,

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

Thanks
MALLI
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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?
-craig

"You can never have too many knives" -- Logan Nine Fingers
mallikharjuna
Participant
Posts: 81
Joined: Thu Nov 30, 2006 7:46 am
Location: india

Post by mallikharjuna »

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.....
MALLI
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

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
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
sensiva
Premium Member
Premium Member
Posts: 21
Joined: Tue Aug 22, 2017 10:39 am

Post by sensiva »

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
sen
Post Reply