How do I use DB2 Explain?

问题:

How do I use DB2's Explain function? -- both to run it, and to use it to optimize queries. Is there a better tool available for DB2?

I've built queries before, but the only way I've had to tell how long they'd take is to run them and time them -- which is hardly ideal.

Edit: The answer for me turned out to be "You can't. You don't have and cannot get the access." Don't you love bureaucracy?

回答1:

What you're looking for is covered by two DB2 utilities:

1. The explain utility, which shows the optimizer's access plan and estimated cost for a specific query (based on current RUNSTATS statistics)
2. The design advisor, which recommends structural changes to improve the performance of one or more queries

Both utilities require specialized tables to be created in the database.

I tend to use the explain utility more than the advisor, especially if I am able to change the SQL for the query to influence a better access plan. I use the db2expln command-line utility to explain different versions of a query I'm tuning and compare their costs. What's most important is that your table and index statistics are up to date when running explain or the design advisor.

回答2:

IBM offers Data Studio as a free tool built on eclipse, which among other benefits includes a GUI for running visual explain, as well as providing tuning help through a query adviser. I highly recommend using Data Studio.

It is relatively easy to set up the correct resources (the explain tables that need to be built, and the bind that need to be done) by right clicking a connected data source, and choosing

analyze and tune > configure for tuning > guided configuration.

To generate the explain graph - simply highlight your query, right click, and choose "Open Visual Explain":

To use the query advisor, choose "start tuning" instead. It will take you through a process which will generate the explain, as well as recommend any tuning opportunities it can determine.

回答3:

db2expln -database sample -t -g -f "sql query"

This worked fine for me.

回答4:

I'\ve always found running the explain utitlity from the command line with '-g' mode turned on has helped me pinpoint problems.

I can't recall the options now, and IIRC, db2expln is a little fussy about the ordering {i.e you can't put username after password!!} - but it works well.