
EXPLAIN Options (DB2 UDB for UNIX and Windows)
The following options are available on the Display Execution Plan for SQL Statement screen:
Button |
Description |
|
If no operator in the access plan is higlighted when choosing this option, a dialog box is displayed providing detailed information on the statement and each operator. This output is similar to the one of the DB2 command line tool db2exfmt . For more information, see the IBM documentation Administration Guide, Appendix I.If operator no. 0 is highlighted, only the original statement and optimized statement are displayed in a separate dialog box. If any other operator is highlighted, the system displays detailed information on the selected operator only. |
|
The access plan may vary depending on the optimizer parameters specified. When you choose this button, the Change Query Optimization dialog box appears where you can change the parameters OPTIMIZER LEVEL, QUERY DEGREE and the attribute VOLATILE for the tables used in the access plan. To explain the statement with new parameters, choose |
|
You use this function to display system catalog information on tables and indexes that are shown in the access plan. The following information is displayed:
Depending on whether you have selected a table or an index, the following buttons are available: -
Columns - Table Displays selected information from table SYSCAT.TABLES. Additionally, all indexes of the table are displayed with their index columns. |
|
With this option, you can display the ABAP Dictionary structure (definition) of a table by selecting the table in the access plan. With this feature, you can display the structure of views, even though views never appear in the access plan. |
|
This option is only available, if a: When you use EXPLAIN, the entered SQL statement is only prepared and the access plan of the optimizer is chosen because of the system catalog statistics. On the basis of this information the optimizer estimates the costs for the execution of this statement. However, the estimated costs may not correspond to the real execution time. Reasons for this might be bad statistics, a bad database layout or problems of the optimizer itself. The Test Execution function measures the real execution time and provides other snapshot data, such as the number of buffer pool accesses and sorts for the selected statement. When the statement is executed, the parameter markers are replaced by the actual parameter values. A dialog box appears where you can change these values to investigate the dependence of the execution time from these values. The result of several test executions of the same statement can vary because, for example, the buffer pool may already contain data that is necessary for the execution. |
|
The following additional information is displayed or hidden: This information is also included in the output information when you choose |
|
When choosing this option, the system switches to an editor window in which you can modify the selected SQL statement and re-explain it. |
|
This option is only available when the statement contains a LOCATION comment, for example, when you call EXPLAIN using transaction ST05 ® Trace List. The location of the statement in the ABAP source code is displayed in a separate window. |
|
For a detailed analysis of a statement you might need to download important information. You can use this option to download data to your local machine (the presentation server) and to execute the DB2 Export on the database server. When you choose Collect EXPLAIN Info, a dialog box appears where you can select data to download: Under Path and Filename on Local Machine you specify the path and file name where the selected information should be stored. The output is stored on your local computer. You can change the path by choosing Browse for folder. The default path is /tmp on UNIX and the value of the environment variable TEMP on Windows. The default file name is <SAPSID>_<current timestamp> . The files are stored in ASCII format.If you want to analyze the download directly on the screen, Display Download on Screen must be activated. By default, this flag is activated. For a more detailed analysis, DB2 support might need the output of the DB2 Export. To display this option, choose The files are stored on the database server in the path and file name you specified under DB2 Export on Database Server. The default file name is sysibm and the default path is /tmp on UNIX and the value of the environment variable TEMP on Windows. The files are stored in IXF format (integrated exchange format), which allows the tables to be recreated for test purposes.You can ignore warnings on truncated data. |