Datastage Loading in Oracle table Speed

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
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Datastage Loading in Oracle table Speed

Post by syedmuhammedmehdi »

Hi,

I'm loading one Oracle table with around 19 million records. I was checking speed of the process by making changes in the job. My job had a fetch query and some sort stages, remove duplicates, joins, a transformer, a funnel and ultimately loads to an Oracle table.

This job was taking around 3 hrs among which 2 hr 15 min was just loading into the Oracle table. Then I split this job in two jobs by changing the Oracle connector load to a dataset and adding a second job moving the dataset to Oracle connector table load.

Both jobs in second case took 49 minutes with only 6 minutes for load to Oracle. I'm wondering why the second case the load took just 6 minutes in comparison with 2 hr 15 min in first case. Could you please advise reason for this?

Thanks

[Note - edited for clarity - Andy]
Last edited by syedmuhammedmehdi on Fri Oct 20, 2017 7:09 pm, edited 1 time in total.
SyedMuhammadMehdi
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

How did you determine that it was 2:15 for the oracle load aspect?

Take your original job, remove oracle target and replace with peek. That should give you the pure transform duration.
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

I removed oracle target and kept dataset in new job after split, will that not give same transform duration? Actually and also in my old job by monitoring link stats at run time the job was just working at load for 2:15 hrs thru. By using these two ways I determined that transform time was just 45 minutes. In both cases it was 45 minutes.
SyedMuhammadMehdi
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

So you are saying that you chopped the job into two pieces by doing a dataset drop, then job number two had two stages... dataset->Oracle and the job #1 ran in 45 mins and Job #2 ran in 6 mins?

Yet you used the same stage properties on the Oracle connector?
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

I have an old topic (from a former user account that quit working) on this exact same type of issue that you may find interesting:

viewtopic.php?t=135492
Choose a job you love, and you will never have to work a day in your life. - Confucius
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

Paul, yes, same stage properties
SyedMuhammadMehdi
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

qt_ky

yes, it is exactly the same thing
SyedMuhammadMehdi
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

Were you loading back into the same table you retrieved data from? (Note - this includes pulling from a view that references the target table).
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
syedmuhammedmehdi
Participant
Posts: 43
Joined: Wed Feb 12, 2014 12:34 pm
Location: Hyderabad, India

Post by syedmuhammedmehdi »

I'm not loading into the same table.
SyedMuhammadMehdi
Post Reply