Browse DevX
Sign up for e-mail newsletters from DevX


DB2 Explains Itself: A Roadmap to Faster Query Runtime-2 : Page 2




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Background: Plans and Costs
DB2 builds plans. A plan is basically the directions that will be followed to the data (or to the shops). To save time, some items on the plan can be performed in parallel (if you have a spouse, they might buy meat while you buy fruit).

Static plans are built once, and used many times—DB2 stores them when you BIND a collection of static SQL. It's like establishing a favorite path for your shopping. You don't have to think about it every time, as you have already decided on a good plan. Plans for dynamic SQL, however, must be evaluated each time. Each plan can be evaluated for its overall cost, which takes into account the nature of the data, and DB2's understanding of the system on which it's running. Critically important in this evaluation are the runtime statistics—the size of your tables, the cardinality of various columns, and the clustering in the indexes.

Figure 1. Color Coding: Here is a sample tree from "Visual Explain."
Figure 2.
Drilling Down: Here is a sample details of a node from "Visual Explain."

Show Me the Plan
There are several tools centered around explaining DB2's plans:
  • Visual Explain is a versatile graphical tool available in the Control Center. It is well suited to examining single queries, whether static or dynamic. Each operation shows as a color-coded node in a tree structure. Clicking on a node allows you to view the arguments, statistics, and cost estimate of the node. You can also jump directly to DB2's documentation on that node type.
  • Visual Explain can also be run from the command line as db2vexp.exe, though it then lacks static SQL ability.
  • Db2expln is the "bare bones" tool, giving text output from static SQL packages only. This can optionally include a character mode graph.
  • Dynexpln gives a text-mode analysis of a dynamic SQL query. It actually packages the dynamic query and calls db2expln to do the work. Listing 1 shows an example of the text output from dynexpln, including a graph.
  • Db2exfmt is a formatter for previously stored explain data.
All these tools use a set of data known as the "explain tables." Visual Explain is capable of creating these tables on demand. With the other tools, you'll need to run a script such as in Listing 2:

C:\PROGRA~1\SQLLIB\bin>cd ..\misc C:\PROGRA~1\SQLLIB\misc>db2 connect to sample Database Connection Information Database server = DB2/NT 7.2.2 SQL authorization ID = GRNASH Local database alias = SAMPLE C:\PROGRA~1\SQLLIB\misc>db2 -tf explain.ddl ******* IMPORTANT ********** USAGE: db2 -tf EXPLAIN.DDL ******* IMPORTANT ********** DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

Now you can assess the results.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date