David M. Uptmor


 View book pages:
 Buy this book:
  MC Press Online
  




A P P E N D I X E Explain

by Roger E. Sanders
A P P E N D I X E 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 results if 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 Enterprise server will show which operations are performed locally and which are performed on the remote server. To see the “ real” access plan, it is necessary to run Explain on the remote database. For example, if the query is embedded in a stored procedure, it will be necessary to copy the query out of the stored procedure and replace parameter markers with hard- coded values to run Explain on the query. The sections that follow describe creating Explain tables, running Explain from the command line, interpreting the output of Explain, and using Visual Explain. EXPLAIN TABLES Before Explain information can be captured, a special set of tables, known as the Explain tables, must be created. Each Explain table used, along with the information it is designed to hold, can be seen in Table E. 1.

Zoom in  zoom  Zoom out
  << Topic >>  | Contents | Index             |<   <<    Page       >>   >|  
A P P E N D I X E 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 results if 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 Enterprise server will show which operations are performed locally and which are performed on the remote server. To see the “ real” access plan, it is necessary to run Explain on the remote database. For example, if the query is embedded in a stored procedure, it will be necessary to copy the query out of the stored procedure and replace parameter markers with hard- coded values to run Explain on the query. The sections that follow describe creating Explain tables, running Explain from the command line, interpreting the output of Explain, and using Visual Explain. EXPLAIN TABLES Before Explain information can be captured, a special set of tables, known as the Explain tables, must be created. Each Explain table used, along with the information it is designed to hold, can be seen in Table E. 1.
Zoom in  zoom  Zoom out
  << Topic >>  | Contents | Index             |<   <<    Page       >>   >|  

MC Press Online - 9781583470800


 Other related titles:
DB2 10.1 Fundamentals: Certification Study Guide (Exam 610)DB2 10.1 Fundamentals: Certification Study Guide (Exam 610)
DB2 9 for DevelopersDB2 9 for Developers
DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide (Exam 731)DB2 9 for Linux, UNIX, and Windows Database Administration: Certification Study Guide (Exam 731)
DB2 9 Fundamentals: Certification Study Guide (Exam 730)DB2 9 Fundamentals: Certification Study Guide (Exam 730)
DB2 9.7 for Linux, UNIX, and Windows Database Administration (Exam 541)DB2 9.7 for Linux, UNIX, and Windows Database Administration (Exam 541)

 Already viewed books:
DB2 9 for Linux, UNIX, and Windows Advanced Database Administration: Certification Study Guide (Exam 734)DB2 9 for Linux, UNIX, and Windows Advanced Database Administration: Certification Study Guide (Exam 734)