Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query, is there anything in DB2 through which I can get the information like, which plans the optimizer considers, the cost of each plan?
I don't know of a way to trace all the plans the optimizer considers. Obviously the
EXPLAIN statement gives you the plan the optimizer eventually selects (and the re-written statement), but that doesn't tell you anything about the ones it doesn't select.
One way to get a little more info might be to set
CURRENT QUERY OPTIMIZATION to different values (the default is 5), run
EXPLAIN on the statement, then set it back to the default (5) and run
If you get different plans with the other optimization levels, this may provide a clue as to the paths DB2 is taking in optimizing your statement (of course, if you get the same plan, it doesn't tell you anything!). I would suspect that the simpler the query the more likely you are to find no difference (so conversely this technique would work better with more complex statements).