Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Apptune for Db2 13.1.

Explain function

To complement workload analysis, 

Apptune

 includes an integrated, robust Explain function.

Explain allows you to dynamically Explain dynamic or static SQL statements. Explain provides both statistical and textual information about the access path along with suggestions on how to improve SQL statement performance. Explain also allows you to edit an SQL statement, then re-Explain it to compare the new access path to the previous path.

You can issue an EXPLAIN command from any SQL statement reported on the SQL Statement Text report or you can use the Explain Object Specification panel.

Explain output is displayed in the Explain Results panel. This panel contains helpful information about improving the performance of the SQL statement. Action codes enable you to display the predicate report, catalog statistics, index key columns, and expert rules that provide advice.

Explain Results panel

A FILE  COMMANDS  OPTIONS  HELP                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------
PSSPW200                                Explain Results for PACKAGE A02620_D_MAIN.PSSYSQL.PSS_1010              1 of 13 Statements
Command ===>                                                                                                      Scroll ===> CSR
                                                                                                                      More:       >
    Actions: S H R RS RW RI XD XS XP W P T C D U IM SA                                                                                
   LBL   STMTNO     COST*RATE SQL-STATEMENT                                                                                       
   XD01      55      3.816278 SELECT NAME , CREATOR , TYPE , DBNAME , TSNAME , COLCOUNT , EDPROC , VALPROC , PCTPAGES , PARENTS ,
         COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBNAME             IXNAME             NU J O G CU J O G LCK PRE CFE ADEG JDEG
   XD01   3.816278  1  1   0 SELECT    0 I      2 N  SYSTABLES          DSNDTX01           N  N N N N  N N N  IS                  
********************************************************** Bottom of Data ********************************************************

You can also issue historical Explains (static Explains from a previous BIND with EXPLAIN (YES)) and retrieve SQL text from the catalog from the SQL Statement Text report. Further, you can issue an EXPLAIN COMPARE on a static SQL statement from the SQL workload. This process performs a dynamic Explain and a static Explain, matching the STMTNO from the catalog to QUERYNO in the PLAN_TABLE.

This section contains the following topic:


Related topics

Explaining-SQL-statements

Report-types



 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Apptune for Db2 12.1