DataStage Server Job - Performance Issue
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
DataStage Server Job - Performance Issue
Hello,
We are using Peoplesoft EPM delivered ETL jobs for loading data from PeopleSoft OLTP table to the Staging area table on Oracle 10g.
DataStage Version : 7.5
OS : HP-UNIX
Job follows CRC logic to load the data. Since this is the initial load there is nothing much happening on the CRC front.
Job Structure:
DRS(SoruceQuery) -> IPC -> Row Merge -> Transformer1 ->Transformer2->Row Split ->Transformer3-> IPC2 -> DRS
This particular job is suppose to load 4,00,000 rows. Initial 3-4 loads ran at 300 rows/sec which was acceptable.
Recently we have started facing issue wherein the job starts at 300 rows/sec and gradually the speed drops. Finally the job comes down to load 15 rows/sec and takes close to 8 hrs. We havent modified anything on the job. Not sure if there is anything on the environment side which could impact the performance.
Note: As the number of rows processed increases the speed keeps dropping with it.
Any suggestions/help in this regard will be appreciated.
Thanks,
Anees
We are using Peoplesoft EPM delivered ETL jobs for loading data from PeopleSoft OLTP table to the Staging area table on Oracle 10g.
DataStage Version : 7.5
OS : HP-UNIX
Job follows CRC logic to load the data. Since this is the initial load there is nothing much happening on the CRC front.
Job Structure:
DRS(SoruceQuery) -> IPC -> Row Merge -> Transformer1 ->Transformer2->Row Split ->Transformer3-> IPC2 -> DRS
This particular job is suppose to load 4,00,000 rows. Initial 3-4 loads ran at 300 rows/sec which was acceptable.
Recently we have started facing issue wherein the job starts at 300 rows/sec and gradually the speed drops. Finally the job comes down to load 15 rows/sec and takes close to 8 hrs. We havent modified anything on the job. Not sure if there is anything on the environment side which could impact the performance.
Note: As the number of rows processed increases the speed keeps dropping with it.
Any suggestions/help in this regard will be appreciated.
Thanks,
Anees
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
Performance Issue
Thanks for the reply,Krazykoolrohit wrote:This happened to me in DB2 and the reason for me was that i was getting lots of warnings. The job took hours to write those warnings.
these warnings are because of load to the table... data issues..metadata mismatch.. try and find the log file.
I checked the DSTG job log and found everything fine. There were no warnings or errors.
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
That is where your problem is. It's always better to seperate inserts and updates (via two seperate links) to load data into table rather than doing it through a single link. They usually(no..not usually..I would rather say always) take long(in fact very very long) times and even worse as the table grows larger. Split your updates and inserts in your job and your job will start flying.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
Peformance
Sure, That is something we will try out.kris007 wrote:That is where your problem is. It's always better to seperate inserts and updates (via two seperate links) to load data into table rather than doing it through a single link. They usually(no..not usually..I would rather say always) take long(in fact very very long) times and even worse as the table grows larger. Split your updates and inserts in your job and your job will start flying.
What is surprising me is the same job ran fine couple of times and suddenly started behaving in this manner.
Since we are using PeopleSoft delivered ETL jobs any customization is owned by the vendor. This is a issue we face when we modify anything on the delivered jobs.
-
- Participant
- Posts: 8
- Joined: Wed Oct 11, 2006 1:16 pm
performance
Even the current load is a initial load as we have truncate the target table completely and reloading the data.kris007 wrote:It ran fine for intial loads because, it was an initial load which is to say that your table was empty and all were inserts. But, as your table grows eventually, it will perform Insert else Update action for every row which is why it will take longer.
Re: performance
Check the free space/current activities at unix. I faced this issue, the problem was that because there were many activities going on the unix box there was not much space left.
Re: performance
Upserts can take up a lot of time. Another issue that can be looked into is the various indices and other constraints enforced in the target. Drop them and perform your inserts and updates (separate jobs). You will definitely see an increase in the performance. Try playing around with the Array size and rows per transaction also.
gateleys
gateleys
Re: performance
Since nothing in the job/database design has changed and the performance has been dropped, not sure changing job/database will help.
10 G is not sufficient to process large amount of data.
10 G is not sufficient to process large amount of data.
Re: performance
Errr... ok. I'm sure the issue here is a little more specific than that, not just the fact that it is "10 G".dsscholar wrote:10 G is not sufficient to process large amount of data.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 232
- Joined: Fri Aug 04, 2006 1:20 am
- Location: Bangalore