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:

   Note

   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 (or, if you have not applied PTF BQU0782, the DBRM field). You do not need to remove any unrelated values before proceeding.

   PSSPA110 ---------------- Explain Object Specification ------------------------
   Command ===>                                                                   
                                                                                 
   Type  . . 5  (1=Plan, 2=Package, 3=UNAVAIL, 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:

   ISREDDE2   EXPLAIN                                         Columns 00001 00072
   Command ===>                                                  Scroll ===> PAGE
   ****** ***************************** Top of Data ******************************
   ==MSG> -Warning- The UNDO command is not available until you change            
   ==MSG>           your edit profile using the command RECOVERY ON.              
   000001 SELECT * FROM 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:

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

   Tip

   (SPE2010) 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:

     FILE   COMMANDS   OPTIONS   HELP
   -------------------------------------------------------------------------------
   PSSPE200             Explain Results for SQLTEXT                               
   Command ===>                                                  Scroll ===> CSR
                                                                    More:       >
      Actions: S H R RS RW RI XD XS W P T
      LBL   STMTNO     COST*RATE SQL-STATEMENT                                    
      XD01          13489.976562 SELECT * FROM SYSTABLES ;                        
            COST*RATE QB PL MIX QTYPE  METH ACC MTCH IX TCREATOR TNAME            
      XD01  13489.976  1  1   0 SELECT    0 I      0 N  SYSIBM   SYSTABLES        
   ******************************** Bottom of Data *******************************

6. Specify options on the Explain Results panel.

   Note

   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.