One of our customers recently performed a migration from a V5R4 system to 7.1
Following the upgrade one of their queries started taking 20 minutes rather than about 2 seconds.
I investigated the query before and after migration and found a few strange things that I hope somebody can explain:
The 5.4 query was optimized by CQE because of a Select/Omit Logical file. The 7.1 query was, as expected, optimized by SQE so I don't understand the huge performance degradation.
The 7.1 query was using a completely different index and access plan to the 5.4 query. Why would this happen, surely the optimizer could always use the existing indexes and access plan.
This problem was reported to IBM and an index was suggested that was not (as far as I can tell) in the index advisor. When this was created the query performed well. Why wasn't the index in the advisor?
Hope somebody can shed some light on these issues.