DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 42
Location: Hyderabad, India
Points: 395

Post Posted: Wed Oct 18, 2017 9:52 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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]

_________________
SyedMuhammadMehdi

Last edited by syedmuhammedmehdi on Fri Oct 20, 2017 7:09 pm; edited 1 time in total
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1153

Points: 7639

Post Posted: Wed Oct 18, 2017 11:28 am Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 42
Location: Hyderabad, India
Points: 395

Post Posted: Wed Oct 18, 2017 11:33 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1153

Points: 7639

Post Posted: Wed Oct 18, 2017 11:43 am Reply with quote    Back to top    

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?
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2653
Location: USA
Points: 19683

Post Posted: Wed Oct 18, 2017 1:14 pm Reply with quote    Back to top    

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:

http://www.dsxchange.com/viewtopic.php?t=135492

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
Rate this response:  
Not yet rated
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 42
Location: Hyderabad, India
Points: 395

Post Posted: Wed Oct 18, 2017 6:50 pm Reply with quote    Back to top    

Paul, yes, same stage properties

_________________
SyedMuhammadMehdi
Rate this response:  
Not yet rated
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 42
Location: Hyderabad, India
Points: 395

Post Posted: Wed Oct 18, 2017 6:59 pm Reply with quote    Back to top    

qt_ky

yes, it is exactly the same thing

_________________
SyedMuhammadMehdi
Rate this response:  
Not yet rated
asorrell
Site Admin

Group memberships:
Premium Members, DSXchange Team, Inner Circle, Server to Parallel Transition Group

Joined: 04 Apr 2003
Posts: 1636
Location: Colleyville, Texas
Points: 22245

Post Posted: Thu Oct 19, 2017 10:39 am Reply with quote    Back to top    

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 - 2017
Rate this response:  
Not yet rated
syedmuhammedmehdi
Participant



Joined: 12 Feb 2014
Posts: 42
Location: Hyderabad, India
Points: 395

Post Posted: Fri Oct 20, 2017 7:06 pm Reply with quote    Back to top    

I'm not loading into the same table.

_________________
SyedMuhammadMehdi
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours