IBM DATASTAGE SLOW PERFORMANCE DUE TO DOUBLE DataType in Updates and Deletes, ORACLE CONNECTOR

Hi,

Are you facing extremely slow performance in Oracle Connector when you try to update a table in Oracle using column that has (NUMBER) data type as the Filtering condition (where clause)?

My friend, look no further this is a common problem me and my team faced using the latest Datastage version. This Datastage double data type even locks the table in addition to being extremely slow.

Why is this happening? In Oracle the NUMBER data type without any precision or scale is kind of an all purpose float datatype that can store any sorts of number from negative digits to numbers with lots decimal places.

This causes Datastage to interpret it as DOUBLE data type is pretty much a floating numeric datatype.

During Inserts you will not notice any performance hit
However during updates or Deletes where the filtering condition uses a column with double datatypes. You will notice how slow and nonfunctional it is..

To fix the situation we decided to remap the column definition of the Oracle table from  Datastage Double datatype to Decimal (38,10). This way we are sure not to run into this issue. Converting it from a floating datatype to Decimal which is more precise boosts the performance and prevents locks on the table.

This can be easily done in the Table definition import by using Orchestrate plug in for Oracle instead of the wizard

IBM DATASTAGE SLOW PERFORMANCE DUE TO DOUBLE DataType in Updates and Deletes, ORACLE CONNECTOR

Hi,

Are you facing extremely slow performance in Oracle Connector when you try to update a table in Oracle using column that has (NUMBER) data type as the Filtering condition (where clause)?

My friend, look no further this is a common problem me and my team faced using the latest Datastage version. This Datastage double data type even locks the table in addition to being extremely slow.

Why is this happening? In Oracle the NUMBER data type without any precision or scale is kind of an all purpose float datatype that can store any sorts of number from negative digits to numbers with lots decimal places.

This causes Datastage to interpret it as DOUBLE data type is pretty much a floating numeric datatype.

During Inserts you will not notice any performance hit
However during updates or Deletes where the filtering condition uses a column with double datatypes. You will notice how slow and nonfunctional it is..

To fix the situation we decided to remap the column definition of the Oracle table from  Datastage Double datatype to Decimal (38,10). This way we are sure not to run into this issue. Converting it from a floating datatype to Decimal which is more precise boosts the performance and prevents locks on the table.

This can be easily done in the Table definition import by using Orchestrate plug in for Oracle instead of the wizard

Converting SQL Real Datatype to NUMBER in SSIS

Problem: I have a column in SQL with Real Data type and I want to transfer it to Oracle in a NUMBER data type. In SSIS if I cast the Real data type to Float it generates unwanted float digits

Problem Image:

SOLUTION

This is actually not an issue. You can copy the float at it is. However, due to certain applications that do computation with the values in the column you might want to cast the REAL data type column to DECIMAL (38,10)

Converting SQL Real Datatype to NUMBER in SSIS

Problem: I have a column in SQL with Real Data type and I want to transfer it to Oracle in a NUMBER data type. In SSIS if I cast the Real data type to Float it generates unwanted float digits

Problem Image:

SOLUTION

This is actually not an issue. You can copy the float at it is. However, due to certain applications that do computation with the values in the column you might want to cast the REAL data type column to DECIMAL (38,10)