estimated execution plan

[Ina Schmitz]

Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the *estimated*
execution plan? Didn't find any distinction of actual or estimated execution
plans in the information center.

Thanks for help,
Ina

[Mark A]

"Ina Schmitz" <we*@inalein.net> wrote in message
news:dt*************@news.t-online.com...

Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the
*estimated* execution plan? Didn't find any distinction of actual or
estimated execution plans in the information center.

Thanks for help,
Ina

The explain should show you the actual execution plan if the dynamic SQL was
submitted at that same point in time, assuming that the optimization level
is the same for the explain and the client that runs the SQL.

If the SQL statement is statically bound, then the execution plan was
created at bind time and will not change unless a rebind is performed.

[Knut Stolze]

Mark A wrote:

"Ina Schmitz" <we*@inalein.net> wrote in message
news:dt*************@news.t-online.com...

Hi NG,

does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the
*estimated* execution plan? Didn't find any distinction of actual or
estimated execution plans in the information center.
The explain should show you the actual execution plan if the dynamic SQL
was submitted at that same point in time, assuming that the optimization
level is the same for the explain and the client that runs the SQL.

In short: DB2 does have only one plan for a SQL statement: the actual one.
There is no mechanism to come up with an "estimated plan".
If the SQL statement is statically bound, then the execution plan was
created at bind time and will not change unless a rebind is performed.

And for dynamic SQL DB2 will take the execution plan from the statement
cache if it is still there (that's why you should flush that cache after
the statistics are collected).

--
Knut Stolze
DB2 Information Integration Development
IBM Germany