Appendix F. Explain Tools

For SQL tuning, it is often necessary to determine the access path chosen by the optimizer for the query. Under the federated database architecture, the best performance is achieved when the query is passed through to the remote database and all of the steps of the access plan are executed on the remote database.

EXPLAIN on the DB2 ESE server will show which operations are performed locally and which are performed on the remote server. To see the "real" access plan, it will be necessary to run EXPLAIN on the remote database.

NOTE

Without a minimum of DB2 on OS/390 experience, the easiest way to get DB2 on OS/390 EXPLAIN results is to use Visual EXPLAIN for DB2 on OS/390.

For example, if the query is embedded in a stored procedure, it will be necessary to copy the query out of the stored procedures and replace parameter markers with hard-coded values to run EXPLAIN on the query.

The sections below describe how to create the EXPLAIN tables, how to run EXPLAIN from the command line, how to interpret the output of EXPLAIN, and how to use Visual EXPLAIN on a federated system.