Chapter 25. Using EXPLAIN

Chapter 25. Using EXPLAIN

IN THIS CHAPTER

• How EXPLAIN Works

• Access Paths and the PLAN_TABLE

• Cost Estimates and the DSN_STATEMNT_TABLE

• Function Resolution and the DSN_FUNCTION_TABLE

• EXPLAIN Guidelines

You can use the EXPLAIN feature to detail the access paths chosen by the DB2 optimizer for SQL statements. EXPLAIN should be a key component of your performance monitoring strategy. The information provided by EXPLAIN is invaluable for determining the following:

• The work DB2 does "behind the scenes" to satisfy a single SQL statement

• Whether DB2 uses available indexes and, if indexes are used, how DB2 uses them

• The order in which DB2 tables are accessed to satisfy join criteria

• Whether a sort is required for the SQL statement

• Intentional table space locking requirements for a statement

• Whether DB2 uses query parallelism to satisfy an SQL statement

• The performance of an SQL statement based on the access paths chosen

• The estimated cost of executing an SQL statement

• The manner in which user -defined functions are resolved in SQL statements