Explain equivallent in DB2

merik · #1 (**permalink**) 04-05-11, 13:41

I'm trying to analyze a query to see how it is using the indexes and how it can be improved. I'm new to DB2 but I have a vast experience in using MySQL, where I'd use the EXPLAIN command to analyze a select query.

What is the equivallent command in DB2? If there is none, what is the common practice in analyzing queries?

I'm using DB2/LINUXX8664 9.7.0 as the server and Ubuntu 10.04 as the client, on which I have IBM Data Studio installed.

dav1mo · #2 (**permalink**) 04-05-11, 13:45

its called explain here as well. You can, also, use visual explain or any number of other tools.

Marcus_A · #3 (**permalink**) 04-05-11, 14:09

EXPLAIN has been in DB2 all the way back to when Bill Gates only bathed once a week (if his co-workers were lucky).

merik · #4 (**permalink**) 04-05-11, 19:37

Thanks for your prompt response. If that's the case, then can you please advise me on the correct format of explain queries (an easy to understand link would be fine). I tried "explain select field from table" but it returned an error message.

n_i · #5 (**permalink**) 04-06-11, 08:51

Quote:

Originally Posted by merik

advise me on the correct format of explain queries (an easy to understand link would be fine). I tried "explain select field from table" but it returned an error message.

That's what manuals are for.

EXPLAIN - IBM DB2 9.7 for Linux, UNIX, and Windows

merik · #6 (**permalink**) 04-06-11, 11:19

I got this error message:

The required Explain table "ABC123.EXPLAIN_INSTANCE" does not exist.

Where ABC123 is my user account on the server. How can I fix it?

n_i · #7 (**permalink**) 04-06-11, 11:36

I think you should really get used to reading manuals

Code:

>db2 ? SQL0219N


SQL0219N  The required Explain table "<name>" does not exist.

Explanation:

The Explain facility has been invoked but was unable to find the
required Explain table "<name>". The Explain tables must be created
prior to invoking Explain.

User response:

Create the required Explain tables. The SQL Data Definition Language
statements needed to create the Explain tables are available in the file
called EXPLAIN.DDL in the misc directory under sqllib.

 sqlcode: -219

 sqlstate: 42704

While you are at it, you may want to also read about the db2exfmt utility - you will need it to retrieve plan information.

Check out db2expln too.