Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
db2expln vs explain plan
760 views
Skip to first unread message
Wendy
Nov 30, 2011, 12:11:17 PM11/30/11
to
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
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
Helmut Tessarek
Nov 30, 2011, 7:30:16 PM11/30/11
to
Hi Wendy,
> What am I missing here? Does db2expln not populate the explain
> tables ... or am I doing something wrong?
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
> What am I missing here? Does db2expln not populate the explain
> tables ... or am I doing something wrong?
Furthermore the output of db2exfmt has more detailed optimizer information.
--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab
Wendy
Dec 1, 2011, 9:23:57 AM12/1/11
to
optimizer info, I would run db2expln then db2exfmt --- seems
reasonable -- explain then format the explain?
Thanks for the response.
Wendy
Helmut Tessarek
Dec 1, 2011, 11:49:54 AM12/1/11
to
Hi Wendy,
On 12/01/2011 09:23 AM, Wendy 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?
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... :-)
On 12/01/2011 09:23 AM, Wendy 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?
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... :-)
0 new messages