Similar to Oracle's EXPLAIN PLAN statement and MySQL's EXPLAIN statement, DB2 provides an EXPLAIN PLAN statement which generates information about the access plan chosen for the specified statement. The information is then placed into explain tables for reference. However, there are times when you simply want to see the explain plan displayed in a hierarchical format with cost information; that's where db2expln comes in handy.

The simplest method of generating and displaying the plan and cost information is to place your query in a file (expln.sql) and execute the db2expln command.

db2expln -d sample -z \; -f expln.sql -t

The output is as follows: