Can someone please explain the difference between the following;

db2expln -d mydb -u myuser mypw -o outputfile -q "select * from
mytable"

and

db2 "explain plan for select * from mytable"


Ultimately what I want to do is to use db2exfmt but if I run db2expln
the explain tables are not populated. When I used "explain plan"
they are.

What am I missing here? Does db2expln not populate the explain
tables ... or am I doing something wrong?

Thanks!

Wendy

Hi Wendy,

You are correct, db2expln does not populate the explain tables.

Furthermore the output of db2exfmt has more detailed optimizer information.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

On Nov 30, 7:30*pm, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Ok, thanks. I was under the impression that in order to get the plan/
optimizer info, I would run db2expln then db2exfmt --- seems
reasonable -- explain then format the explain?

Thanks for the response.

Wendy

Hi Wendy,

On 12/01/2011 09:23 AM, Wendy wrote:
db2expln is rather a quick and dirty method of getting a plan and
optimizer info. At least that's the way I always saw it. db2expln takes
your statement or input file and gives you the output in one step. The
optimizer info is not too detailed but is definitely good enough for a
first glance at the access plan.

If you want more detailed information or want to drill down into the
depths of optimizer info, you will have to use db2 explain.
You can use the CURRENT EXPLAIN MODE special register to control the
behavior of the explain command. - http://j.mp/vKUf7W
After you have collected the data, you can format the data by using
db2exfmt.

Please be aware that db2exfmt will give you all the data you ever wanted
- and more (it will also give you all the data you do not want to
know... :-)

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab