We've run into a strange situation that has us scratching our heads. We have a DB2 package that had not been rebound for several versions of DB2 (I know this is bad) but was automatically rebound after we upgraded to DB2 v11 CM. After the upgrade, the access path changed with the order of the 2 indexes being swapped. This resulted in the run time and CPU time both increasing by 5-6 times.
The package did not use a hint but we tried to add one pointing to the old access path. When we do this, even though the bind says it used the hint, the rows in the PLAN_TABLE still list the indexes in the post v11 order resulting in the longer execution and CPU times.
We were able to get the hint to work on one of our development systems the way that we want it to but we can't get it to work on another dev system or on the prod system.
We have taken a look at the DB2 zparms and they appear to be the same across subsystems except for values that you'd expect to be different. We've also tried various things including updating stats and changing one of the indexes but we still can't get the hint to work or the access path back to where it was before the v11 bind.
Does anyone have any ideas of what we should try or look at to get the access path back to where we want/need it to be or to determine why the hint is working as expected on the 1 dev subsystem/DS group but not on the others?
Thanks in advance.