EXPLAIN Options (DB2 UDB for UNIX and Windows) (SAP Library - CCMS: SAP/DB2 UDB for UNIX and Windows)

EXPLAIN Options (DB2 UDB for UNIX and Windows)

On the Display Execution Plan for SQL Statement screen, the following options are available:

Option	Description
Details	If no operator in the access plan is highlighted 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.
Optimizer	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 flag VOLATILE for the tables used in the access plan. To explain the statement with new parameters, choose Explain Again.
DB Catalog	With this option, you can display system catalog information on tables and indexes that are shown in the access plan. The following information is displayed: · For a table: Selected information from table SYSCAT.TABLES is displayed. Additionally, all indexes of the table are displayed with their index columns. · For an index: Selected information from table SYSCAT.INDEXES for this index is displayed. Additionally, selected information from table SYSCAT.COLUMNS is displayed for all index columns. Depending on whether you have selected a table or an index, the following buttons are available: · Table Displays selected information from table SYSCAT.TABLES. Additionally, all indexes of the table are displayed with their index columns. · Columns Displays selected information from table SYSCAT.COLUMNS for all table columns · Indexes Displays information from table SYSCAT.INDEXES for all indexes of the table as well as information from table SYSCAT.COLUMNS for all index columns · Update Statistics Updates the catalog statistics for the table. If the catalog statistics were updated successfully, the field <stats-time> is displayed in green. · Table Displays selected information from table SYSCAT.TABLES. Additionally, all indexes of the table are displayed with their index columns.
Dictionary	With this option, you can display the ABAP Dictionary structure (definition) of a table by selecting the table in the access plan. If you do not select a table in the access plan, the ABAP Dictionary structure (definition) of the first dictionary object of the SQL statement is displayed. With this option, you can display the structure of views, even though views never appear in the access plan. This function is only available for RFC-monitored systems.
Test Execution	This option is only available, if a: · SELECT statement is explained using transaction ST05 ® Trace list, the parameter values for all parameter markers of the statement are provided and the operation is other than PREPARE. · SELECT statement without parameter markers is explained. When you use the EXPLAIN function, 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 option 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. This function is only available for RFC-monitored systems.
Tree Info	The following additional information is displayed or hidden: · num_rows Estimated number of rows (result set) · tot_cost Estimated total cost for this statement · i/o_cost Estimated I/O cost of the statement This information is also included in the output information when you choose Details.
Edit	When choosing this option, the system switches to an editor window in which you can modify the selected SQL statement and re-explain it.
Source	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. This function is only available for RFC-monitored systems.
Collect	For systems that are monitored using remote database connections, this function cannot collect all necessary information. Therefore, a couple of error screens will appear when you execute this function. However, you should ignore these error screens and the function continues to gather as much information as possible. 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 Information, a dialog box appears where you can select data to download: · DB2 Level · Registry Variables · DBM Configuration Database manager configuration · DB Configuration Database configuration · Table Structure Structures of the tables involved in the statement and all views defined in the database · Tablespace Configuration · Statistics Statistics of the tables involved in the statement · Explain EXPLAIN information of executable db2exfmt including the access plan 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 Display More/Less. By default, this option is deactivated. To activate it, select DB2 Export on DB Server and choose Continue. 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.