Dropping EXPLAIN tables

Db2® Admin Tool can drop all of your EXPLAIN tables by calling the Db2 stored procedure ADMIN_EXPLAIN_MAINT. Alternatively, you can select individual EXPLAIN tables to drop, in which case Db2 Admin Tool issues DROP TABLE statements.

Before you begin

You must have the following Db2 APARs applied:

Procedure

To drop EXPLAIN tables:
  1. On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.
  2. On the EXPLAIN (ADB2E) panel, specify option MNT or M (for maintenance), and press Enter.
  3. On the Manage EXPLAIN Tables & Aliases (ADB2EM1) panel, set the Manage alias field to YES and then set the Schema alias field to blank.
    The Schema alias column lists all existing aliases on PLAN_TABLE and any aliases on the other EXPLAIN tables that are the same as the PLAN_TABLE aliases. Review the list of aliases for those tables that you want to drop.
  4. On the Manage EXPLAIN Tables & Aliases (ADB2EM1) panel, take one of the following actions:
    • If you want to drop individual tables, issue the DT line command against those tables.

      Also, if any aliases exist on those tables, use the DA line command to drop any aliases as needed.

    • If you want to drop all EXPLAIN tables:
      1. Specify DROPALL in the Action field. DROPALL drops all selected tables and their aliases.
      2. Issue the RUN (or R) command.
        Tip: If you want to preview the DROPALL operation first, issue the PREVIEW (or P) command.
      Figure 1. Manage EXPLAIN Tables & Aliases (ADB2EM1) panel
      ADB2EM1 n           DD1A Manage EXPLAIN Tables & Aliases     Row 1 to 12 of 23 
Command ===> R                                                 Scroll ===> PAGE 
                                                                               
Commands: RUN  PREVIEW                    
Line commands: S - Select  U - Unselect  M - Show message  T - Table           
               DT - Drop table                                                 
Input for ADMIN_EXPLAIN_MAINT stored procedure:                                
 Action . . DROPALL  (Create/Upgrade/Dropall)  Manage alias . . NO  (Yes/No)    
 Schema . . TS6462                                                           > 
LC S W EXPLAIN Table              Schema         Owner                         
       *                          *              *                             
-- - - -------------------------- -------------> ------------->                
   S   PLAN_TABLE                 Not found                                    
   S   DSN_STATEMNT_TABLE         Not found                                    
   S   DSN_STATEMENT_CACHE_TABLE  Not found                                    
       DSN_QUERYINFO_TABLE        Not found                                    
       DSN_PREDICAT_TABLE         Not found                                    
       DSN_FILTER_TABLE           Not found                                    
       DSN_DETCOST_TABLE          Not found                                    
       DSN_COLDIST_TABLE          Not found                                    
       DSN_KEYTGTDIST_TABLE       Not found                                    
       DSN_VIRTUAL_INDEXES        Not found                                    
       DSN_VIRTUAL_KEYTARGETS     Not found                                    
       DSN_FUNCTION_TABLE         Not found

      The results from calling the ADMIN_EXPLAIN_MAINT procedure are displayed on the Execution Results (ADB2EM2) panel. Issue the S line command against the result sets to view more details about the objects that were created.