PX jobs performing slow
Moderators: chulett, rschirm, roy
PX jobs performing slow
I need few ideas on increasing the performance of my huge volume on time loads.
Back ground:
We have very huge volumes( 47 M - 2000M)
Our database is db2 8.2. it is on a Aix box and Datastage is on a solaris box Due to this hetrogineous environment we cannot use DB2 EE stag ein our PX jobs So we were forced to use DB2 API stage in our PX jobs.
In some of the jobs that are just inserts only we have tries a work arround with bulk loader but in other jobs which are updates else inserts we are forced to use db2 API stage and these jobs are performing very very slow.
For analysis I removed the target DB2 API stage and replaced it with a dataset and the seeds were much heigher at about 1000 rec/sec compared to the speeds with a target DB2 stage which were 200 rec/sec.
Please through any ideas that I can incorporate to improve the performance of the jobs.
O/S: sunsolaris 2.8
SMP 4 Nodes
( We have good disc space available and MMEMOFF,DMEMOFF,CMEMOFF and PMEMOFF settings)
Back ground:
We have very huge volumes( 47 M - 2000M)
Our database is db2 8.2. it is on a Aix box and Datastage is on a solaris box Due to this hetrogineous environment we cannot use DB2 EE stag ein our PX jobs So we were forced to use DB2 API stage in our PX jobs.
In some of the jobs that are just inserts only we have tries a work arround with bulk loader but in other jobs which are updates else inserts we are forced to use db2 API stage and these jobs are performing very very slow.
For analysis I removed the target DB2 API stage and replaced it with a dataset and the seeds were much heigher at about 1000 rec/sec compared to the speeds with a target DB2 stage which were 200 rec/sec.
Please through any ideas that I can incorporate to improve the performance of the jobs.
O/S: sunsolaris 2.8
SMP 4 Nodes
( We have good disc space available and MMEMOFF,DMEMOFF,CMEMOFF and PMEMOFF settings)
M.T.Anwer
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
I would expect that a DB2 client on Solaris would be able to connect to a DB2 server on AIX, and therefore the EE stage could use that client connection. Have you got a copy of the DB2 Remote Configuration Guide? I noticed someone uploaded the latest version to developernet just a week or two ago.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Thanks ray... but in order to seperate inserts and updates I have to again do a look up on the target to distinguish with between inserts and updates. And there by increasing changes of table scanning since my target would have to also serve as look up. THis has the potential of decreasing performance.
M.T.Anwer
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
Thanks Buney,
DB2 EE stage cannot be used to connect to a remote Database which is on a different operating system. ex ETL server is solaris and DB server is on AIX. We have turned every page to try to make DB2 EE work.
DB2 EE stage cannot be used to connect to a remote Database which is on a different operating system. ex ETL server is solaris and DB server is on AIX. We have turned every page to try to make DB2 EE work.
M.T.Anwer
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
Are you using a Join Stage or a Lookup Stage to do your joins. The performance on Join Stage is not that good as compared to Lookupstage. Where as the Join Stage can handle bigger size of data than Lookupstage.
If you are using any plug-in stage, this surely would be lagging your performance. And again as Ray has mentioned, you need to optimize your design for better performance.
And can you exactly tell where is the bottleneck and what is the design there.?
If you are using any plug-in stage, this surely would be lagging your performance. And again as Ray has mentioned, you need to optimize your design for better performance.
And can you exactly tell where is the bottleneck and what is the design there.?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To determine existence all you need is to pre-load a Lookup File Set with only the key columns of the target table. You will be surprised how quickly this will work.MTA wrote:Thanks ray... but in order to seperate inserts and updates I have to again do a look up on the target to distinguish with between inserts and updates. And there by increasing changes of table scanning since my target would have to also serve as look up. THis has the potential of decreasing performance.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
DSGuru, Yes I am using join stage as I am dealing with very large volumes. The real bottel neck is in making in the target DB2 API STAGE.
The inserts and updates are happenning very very slow.
The inserts and updates are happenning very very slow.
M.T.Anwer
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
Thanks every one for your input. Your advise has got me to th epoint where I was able to fine tune the jobs
M.T.Anwer
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
The day the child realizes that all adults are imperfect he becomes an adolescent;
the day he forgives them, he becomes an adult; the day he forgives himself, he becomes wise.
-Aiden Nowlan
MTA,
So the LookUp File Set help you fine tune the job via spliting the Update and Insert stream? Please tell us what has helped you fine tune it, so we can learn from it.
Thanks!
So the LookUp File Set help you fine tune the job via spliting the Update and Insert stream? Please tell us what has helped you fine tune it, so we can learn from it.
Thanks!
Pneuma Lin.
pneumalin@yahoo.com
pneumalin@yahoo.com