We have three environments, production and two acceptance testing. One of the DB2 INSERT query (with subselect) runs quickly in production and one of testing environment (say X). it takes around 1 min to finish, However on intermittant basis on another testing environment (Say Y), the same query runs extremely longer (around 9 to 13 hours).
Surprising thing is, the query run longer only on intermittant basis and that too only one environment Y.
One more important thing is, when the query runs longer on environment Y, We usually kill that job and restart it again. upon restart the query gets completed within 1 min or so.
This is really strange behaviour and we are now clueless, customer is really behind us considering this issue.
We tried to investigate with DBA's and below recommneded change was applied to environment Y.
The Default query optimization class (DFT_QUERYOPT) = 7 was changed to Default query optimization class (DFT_QUERYOPT) = 5. But it didnt make any difference and we are still stuck into the issue.
Its hard to debug as the problem applies to only one environment and on that too its intermittant.
There was also one more observation when the query was executing, the query seems to read large number of rows, that was visible in db2top output provided by DB2 DBA.
I am sure, there is not going to be one liner answer to this, however if there is any debugging approach someone can suggest on this forum, that would be really helpfull.
Hope to hear soon from experts on this group.