DataStage int32 vs decimal(10,0)

meriem_ens · Post by **meriem_ens** » Fri Aug 29, 2014 2:28 am

Hi,

Memory wise, what gives the best performance, declaring a column int32 or decimal(10,0) ?

I'm asking myself this question because I need to load a sql server table into oracle

The table consists of columns with the int datatype. In sql server the int datatype is like a number(10) in oracle. In my job what would be better (performance but also best practice)

1) read the sql server column with datastage integer datatype, then write the column to oracle with the datastage decimal(10,0) datatype

or

2) read the sql server column with the datastage decimal(10, 0) datatype, then write the column to oracle with the decimal(10,0) datatype.

thanks

chulett · Post by **chulett** » Fri Aug 29, 2014 9:23 am

I'm not sure any of this affects 'performance' per se. What is your actual Oracle datatype?

Mike · Post by **Mike** » Fri Aug 29, 2014 10:49 am

It's good to see that you have performance awareness, but this is a nit and not something to be concerned with.

Whatever difference there is between an int32 and a decimal(10,0) is probably too small to measure and would get lost in the "noise".

An int32 is 4 bytes and a decimal(10,0) would be 6 bytes if it's in a packed decimal internal format.

To generalize, computer systems handle binary data more efficiently than decimal data.

Save your performance hawking for orders of magnitude differences. For example, jobs that run in hours instead of minutes or days instead of hours.

Mike

meriem_ens · Post by **meriem_ens** » Sat Aug 30, 2014 12:16 pm

chulett wrote:What is your actual Oracle datatype?

It's number(10, 0)

meriem_ens · Post by **meriem_ens** » Sat Aug 30, 2014 12:19 pm

When I used the view data feature of datastage I had this :

- for integer the values are displayed 1, 2, 3, 4
- for decimal(10, 0) the values where displayed 0000000001., 000000002., 0000000003., 0000000004.

I guess it's just a display format and not the internal representation in DataStage.

Thank you

chulett · Post by **chulett** » Sat Aug 30, 2014 3:28 pm

Correct.

ray.wurlod · Post by **ray.wurlod** » Sat Aug 30, 2014 3:42 pm

DataStage, when converting Decimal to String (for example for display or writing to text files), adds leading and trailing zeroes in accordance with the precision and scale settings.

meriem_ens · Post by **meriem_ens** » Sat Aug 30, 2014 3:50 pm

Thank you both.