How do I use DB2 Explain?
db2, explain, query-optimization, sql
Solution
What you're looking for is covered by two Db2 utilities:
- The explain facility, which shows the optimizer's access plan and estimated resource cost for a specific query (based on current RUNSTATS statistics)
- 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.
Problem
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?