How do I use DB2 Explain?

Question

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?

Faveolate

Don't you love DB2? It's absolutely ridiculous that you need certain tables to be present that are not created by default during installation and that can only be created by someone with admin rights just to run a simple EXPLAIN. In MySQL all this administration isn't needed. Why does a paid database work worse than an open source one? – Defensive

While DB2 is not my favorite DB for many reasons, this actually has a valid reason. Allowing random people to explain against a busy enterprise database can affect performance and lock tables - not good if you're tracking real time data for a few million data points. You wouldn't use MySQL for the scale problems that you would consider spending money on DB2 and DB2 administration for. – Bolin

Is "explain" actually more resource-consuming than running the query you want to optimize repeatedly, trying to find the optimal paths blindly? If you're giving random people ad-hoc query access to the database, not allowing them to use explain doesn't sound like it will actually help much. – Faveolate

Answer 1

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

1. The explain facility, which shows the optimizer's access plan and estimated resource 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 facility more than the design advisor, especially if I have the option of changing the underlying SQL of the statement that needs to be tuned. The db2expln command is a convenient way to run the explain facility from the command line for any SQL or XQuery statement. I commonly run db2expln multiple times when comparing the costs of different versions of a statement I'm tuning. It's important that your table and index statistics are up to date when running explain or the design advisor.

Ripon

Answer 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.

Parisparish

This is just awesome. Highly recommend if you don't find it difficult to read text based explain plans – Synopsize

Answer 3

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

This worked fine for me.

Territoriality

Answer 4

I've always found running the explain utility 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.

Grata