IBM DataStage Array Size and Record Count tuning

Hi

I was recently working on extracting a table that has 400 columns from a SQL Server DB to load into an Oracle Target Database. I designed my job set the array size to 50,000 with Record count 200,000. To my surprise the job could not pull the top 1000 records for over an hour.






Resolution

It turns out that based on my environment Datastage settings the buffer size is too small to hold an Array size of 50,000 records (considering that each row has 400 columns). I had to reduce it to 100 Array size so the job could run in 3 mins for 1million records

What is Array size: Array size is the number of rows that datastage reads/writes to the database. This is the number of records that is pushed from one stage to another (Pipeline parallelism)

Record count: is the the number of rows that datastage commits in the database after writing as much array size.

Datastage ETL loads Causing Oracle Database to hang up and crash(Oracle Database server would be inaccessible)

Lately, we faced an issue in my project where we noticed that when running 3 jobs that loaded tables of 3.5 Gb,1Gb,2gb at the same time choked the Oracle Database. The job were ran on 2 nodes, which turns out to be 3 x 2 is 6 sessions in parallel. The Dba noticed I/O waits on the Transaction log. This was due to the number of inserts being two much for the database which cause it to lock up.

We also noticed some strange Oracle sessions with SYS user performing UPDATEs on obj$. the Sessions kept growing after its intial appearance.

Another strange thing was the Oracle Dev Database worked well with the same amount of load, when ran in parallel.



SOLUTION
If you notice that the Oracle Database is hanging up when running Datastage jobs. Try to reduce the number of processes ran in Parallel. A rule of thumb is to run any table more than 3GB as a standalone then run the other two jobs (2gb and 1gb) in Parallel

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)

ODBC DRIVER

ODBC. Note you must have Oracle Client installed in your machine already for this and the driver built for any server you want to deploy it at.
See link for installing Oracle client.
http://www.oracle.com/technetwork/database/features/instant-client/index.html

Another thing that I had to do because the SSIS package ran for more than 48hrs was to increase PGA limit of Oracle to 30GB from default 2GB because my session had to be open for a long time.

DETAILS


Prerequisites for this to be usual guideline
1. SSIS 2012 or later Installed
2. A connection to an SQL Server Database with a table with at least 1 million records (Source Table) for testing purposes
3. A connection to an Oracle Database, where your destination table will be contained
4. Oracle Client 11g or later installed in your machine

step 1:
 GO into DataFlow Task

Step 2:

Select your Source component. In my case it is SQL Server. Usually you might have other transformation in between before your Destination component. In this case the focus is on the destination

Drag the ODBC Destination into the data flow like shown in the picture.

Step 3

Double click on the ODBC Destination. (not shown) Then Click the 'New..' button to create an ODBC connection
You might see this page. if you don't see step 4. Anyway, Click the 'New..' button.. I see this page because I already created ODBC connection in my machine

Step 4:
You will be get another dialogue box for the Connection Manager. All you have to do is to Select the 'Use Connection String' Option and Click on 'Build;

Step 5:

Select the Machine Data Source. Click the New button (as always lol)\

step 6:
Select the Desired type of Data source. I prefer the machine Data source so that your package can be ran by any user on your machine. Click Next

step 7:
Scroll down. Skip the Microsoft ones, and Choose the Oracle in Oraclient11g  or whatever name you used to install the Oracle Client in your machine, Then click Next. Then Finish

step 8
Enter the information as per your Oracle Database. The Data Source Name is up to you to define.

NOTE:  If you plan to deploy your SSIS package to another machine make sure the Data source name matches the name on that machine so that when you install the driver on the other machine your package will not have validation error.. choose whatever configuration in the below box as per requirement. I felt mine with default setting.

step 9
Once you are done. You will eventually get back to this screen below

Step 10: Click a bunch of  'Ok' then select your ODBC Destination tables and start loading data.


This is just one way to do this. There are many other ways to install the Oracle ODBC connector.

like source/https://tensix.com/2012/06/setting-up-an-oracle-odbc-driver-and-data-source/