DB2 create explain tables and check access plan of a query

DB2 provides some commands to visualize the access plan of the queries. To run these commands it needs to creates the explain and and advise tables (working tables) on the database:

db2 connect to DB_NAME

db2 "call SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

This command above create a set of tables under SYSTOOLS schema this is the target default place, but you can change it. Note, the option to delete these tables is 'D' instead of  'C'.    

After creating the tables you can run the command with your statement to analyze:

db2expln -database DB_NAME -schema SCHEMA_NAME -package % -statement "your query" -terminal -graph

or put your statement on a file, put the terminator ";" in the statement:

db2expln -database DB_NAME -schema SCHEMA_NAME -package % -stmtfile query.sql -terminator ";" -terminal -graph

A graph plan is displayed to visualize the access plan of the specified query.

If you want to get advise from DB2 run the command:

db2advis -d DB_NAME -q SCHEMA_NAME -s "thequery" -noxml

http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008441.html?cp=SSEPGG_10.1.0%2F2-9-13