Hello,
we are running DB2 UDB EEE Version 7.2 Fixpack 12 on a two machine
Windows 2000 Advanced Server Cluster in a dss environment.
Some dynamic sql statements for etl processes and even some for online
user queries switched overnight from some minutes runtime to a few
hours or "never come back" statements.
We didn't change a single database or instance parameter. Even the
statistics are still the same for the involved queries. Unfortunately
we cannot compare the before and after access plans because we only
have the actual ones. Query rewriting and index optimization helped us
to tune the bad performing dynamic sqls but we still see bad
performing statements which used to run well.
Most (not all) of the statements are using inner joins and left outer
joins. Some of the tables have more than 100 million rows. However the
statements used to perform well some days ago without changing
parameters. Even the reorgchk output seems to be ok.
My questions:
Has anybody seen a similar behaviour in his/her environment?
What else could affect the access plan for dynamic sql statements?
Are there any issues besides the access plans which may be caused the
performance degradation?
thank you
Florian