actual/estimated cost/cardinality of query plan

[uwcssa@gmail.com]

is there a way to see the actual (not just estimated) cost and
cardinality at each plan operator? SQL Server and Oracle has such
feature but I failed to find a way in DB2 yet (up to V9.5)

[Mark A]

Re: actual/estimated cost/cardinality of query plan

<uwcssa@gmail.c omwrote in message
news:d5fe4867-f6e4-4955-af26-2221d58f406e@31 g2000prz.google groups.com...

is there a way to see the actual (not just estimated) cost and
cardinality at each plan operator? SQL Server and Oracle has such
feature but I failed to find a way in DB2 yet (up to V9.5)

The cardinality is in the catalog tables, particularly syscat.indexes
(assuming you executed runstats "with indexes all" option).

[w.l.fischer@googlemail.com]

Re: actual/estimated cost/cardinality of query plan

On Oct 15, 11:25 pm, "Mark A" <some...@someon e.comwrote:

<uwc...@gmail.c omwrote in message
>
news:d5fe4867-f6e4-4955-af26-2221d58f406e@31 g2000prz.google groups.com...
>

is there a way to see the actual (not just estimated) cost and
cardinality at each plan operator?  SQL Server and Oracle has such
feature but I failed to find a way in DB2 yet  (up to V9.5)

>
 The cardinality is in the catalog tables, particularly syscat.indexes
(assuming you executed runstats "with indexes all" option).

I always get the cardinality of tables and indexes, I think. Please
check if

set current explain mode explain
set current explain snapshot explain
<query>
set current explain mode no
set current explain snapshot no
[...]
db2exfmt ...

makes a difference. The trick is to use both explain mode and explain
snapshot. (Recited from memory, so check syntax.)