PX jobs performing slow

MTA · Post by **MTA** » Tue Apr 04, 2006 10:02 pm

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)

ray.wurlod · Post by **ray.wurlod** » Tue Apr 04, 2006 11:27 pm

Change your job design so that it segregates inserts and updates into separate streams into DB2.

vmcburney · Post by **vmcburney** » Wed Apr 05, 2006 12:43 am

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.

MTA · Post by **MTA** » Wed Apr 05, 2006 9:19 am

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.

MTA · Post by **MTA** » Wed Apr 05, 2006 9:22 am

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.

DSguru2B · Post by **DSguru2B** » Wed Apr 05, 2006 10:10 am

Are you doing any slowly changing while inserting into the target?

MTA · Post by **MTA** » Wed Apr 05, 2006 11:16 am

No DsGuru

DSguru2B · Post by **DSguru2B** » Wed Apr 05, 2006 11:38 am

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.?

ray.wurlod · Post by **ray.wurlod** » Wed Apr 05, 2006 3:13 pm

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.

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 · Post by **MTA** » Thu Apr 06, 2006 12:21 am

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.

MTA · Post by **MTA** » Mon Apr 10, 2006 2:34 pm

Thanks every one for your input. Your advise has got me to th epoint where I was able to fine tune the jobs

pneumalin · Post by **pneumalin** » Mon Apr 10, 2006 2:57 pm

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!