DB2 connector select performance
Posted: Tue Oct 24, 2017 12:34 pm
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?
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?