DB2 connector select performance

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
js103755
Participant
Posts: 7
Joined: Tue Apr 14, 2015 4:16 am
Location: USA

DB2 connector select performance

Post by js103755 »

We have a job which has two DB2 connector stages which run two select queries then the output is fed to funnel stage then it's fed to transformer where the records are separated based on certain conditions and further aggregated on a group of columns.
The select query in the DB2 connectors join multiple tables and calculate the average of amount columns based on certain group of columns.

This job used to take 8 to 10 minutes in a normal day, but suddenly after last migration to QA, it ran for around 3 hours and didn't finish. However in Test it finishes within minutes. I took the queries and ran them manually in SQL window both in test and QA. The test one would finish within 8 minutes but QA would run forever until I cancel them.

Then I discussed with DBA thinking it would be a database issue, he did runstats and reorg on all the tables. After that when I ran the queries in SQL window in QA, it gave result in 7 minutes but still the datastage job took around 2 hours.

I can't understand the difference here, while running in a SQL window manually and while running through job. The only difference I can see is in the job it's running two instance of the query because its 2 node configuration.

What could be the reason for it? How can I improve the perfromance of DB2 select? Can someone help me please? Has anyone ever experienced something like this?
- Jay
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

It sounds like you did confirm there was a database problem that the DBA was able to resolve with runstats and reorg. Perhaps there are multiple problems.

Not knowing exactly where the problem exists, I would start to break the job apart and go with job1 having DB2 Connector stage1 to a Data Set stage1, job2 having DB2 Connector stage2 to a Data Set stage2, and job3 reading Data Set stages 1 and 2, then see where that leads.
Choose a job you love, and you will never have to work a day in your life. - Confucius
js103755
Participant
Posts: 7
Joined: Tue Apr 14, 2015 4:16 am
Location: USA

Post by js103755 »

Thanks for the suggestion, I'll probably do that and see how it goes. The problem is I can't replicate the issue in test environment which makes it difficult and time taking. Anyways I'll try to divide the job and post my findings.
- Jay
Post Reply