Explaining an ad hoc SQL statement

Use the following procedure to Explain an ad hoc SQL statement:

1. Access the Explain Object Specification panel. The method varies depending on the product.

   • On the BMC AMI SQL Explorer for Db2 main menu, select Explain. Review the SSID and DDF Location, revise if necessary, and press Enter.
   • On the BMC AMI Apptune for Db2 Main Menu, select Explain Interface and press Enter.
   • On the BMC AMI Ops Monitor for Db2
      - DC Main Menu, select Explain Interface and press Enter.

   The Explain Object Specification panel is displayed:

   Important

   Explain recognizes only the information that applies to the object Type that you specify.

   Example

   If you specify to Explain a package, the product ignores information in the Plan or DBRMLIB fields. You do not need to remove any unrelated values before proceeding.

   (BMC.DB2.SPE2410)

   PSSPA110 ---------------- Explain Object Specification ------------------------
   Command ===>                                                                   
                                                                                 
   Type  . . 5  (1=Plan, 2=Package, 4=DBRMLIB, 5=Ad Hoc SQL)           
                                                                                 
   Plan:                                                                          
     Name                                                                         
                                                                                 
   Package:                                                                       
     COLLID  GXRALIAS          Name . . . PSSXSQL                           
     Version %                                                                    
                                                                                 
   DBRMLIB:   (Specify PDS with member name or wildcard member.)                  
     DSN . .                                                                      
                                                                                 
   Processing Mode:  B      (L=List object(s),                                 
                                  B=Batch Explain with specified objects)           
                                                                                 
   List Valid and Operative packages ONLY:  N  (Y/N)                              
                                                                                 
     Explain Options  

2. In the Type field, select Ad Hoc SQL and then press Enter.An ad hoc SQL Explain edit session is displayed:
   (BMC.DB2.SPE2410)

   ISREDDE2   EXPLAIN                                         Columns 00001 00080
   Command ===>                                                  Scroll ===> CSR
   ****** ********************************* Top of Data **************************              
   000001   SELECT * FROM SYSIBM.SYSTABLES                                                 
   ****** ******************************** Bottom of Data ************************

3. In the edit window, type an SQL statement and then press F3.The Explain or Execute Parameters panel is displayed:
   (BMC.DB2.SPE2410)

   PSSPA117 ----------------- Explain or Execute Parameters ----------------------
   Command ===>                                                                   
                                                                                 
   Specify the options below and press ENTER to continue.                         
                                                                                 
   Option . . . . 1    1. Explain                                                 
                       2. Execute                                                 
                       3. Edit                                                    
                                                                                 
   Qualifier Name ACMID1
                                                                                 
   ------------------------- Execute  Options -----------------------------------
                                                                                 
    Select rows max.. 1000  (Range 1 to 5000)                                     

   Tip

   You can use the Select rows max option to specify the maximum number of rows that can be retrieved in the Execute options for the Select statement. The default number of rows is 1,000. The maximum range that the select statement can fetch is 5,000.

4. In the Option field, select Explain.

5. Specify a Qualifier Name, and press Enter.The Explain Results panel is displayed:
   (BMC.DB2.SPE2410)

     FILE   COMMANDS   OPTIONS   HELP
   -------------------------------------------------------------------------------------------------------------------------------------------
   PSSPW200                                    Explain Results for SQLTEXT                               
   Command ===>                                                                                                              Scroll ===> CSR
                                                                                                                                More:       >
      Actions: S H K R RS RW RI XD XS XP W P T C D U IM SA
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      XD01       0    178.919800 SELECT * FROM SYSIBM.SYSTABLES                                                                           S.U.                   
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TBAME              IXNAME             NU J O G CU J O G LCK PRE CFE ADEG JDEG APG JPG
      XD01  178.91980  1  1   0 SELECT    0 R      0 N  SYSTABLES                             N  N N N N  N N N  IS S          0        1
   ************************************************************ * Bottom of Data *************************************************************

6. Specify options on the Explain Results panel.

   Important

   For a SQL Performance installation, the identifier of this panel is PSSPW200. In this case, additional action codes are available only as part of the SQL Performance for DB2 solution.

7. Review the results of your Explain.