Upgrading EXPLAIN tables

The format of Db2 EXPLAIN tables can vary in different versions of Db2. Therefore, you might need to upgrade your EXPLAIN tables when you migrate to a new version of Db2 or when you apply certain Db2 maintenance.

To upgrade 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.

   ADB2E min                             Explain                             23:33
   Option ===>                                                                    
                                                                                  
                                                                                  
      E - Explain an SQL statement                          DB2 System: DD1A      
      L - List PLAN_TABLE   Q - List SYSQUERY explain info  DB2 SQL ID: ADM001    
            Schema . . . . . . . . .           >           (default is ADM001)    
            Plan name  . . . . . . .           >           (optional)             
            DBRM/package name  . . .           >           (optional)             
            Collection ID  . . . . .                     > (optional)             
                                                                                  
    DPS - Dynamic Plan Stability                                                  
    SCT - Statement Cache Table  
                   
    MNT - Create, upgrade, or drop EXPLAIN tables via ADMIN_EXPLAIN_MAINT stored 
          procedure                                                             

   Note: The DPS and SCT options are displayed only if the CACHEDYN subsystem parameter is set to YES and the CACHEDYN_STABILIZATION subsystem parameter is set to something other than NONE.

3. On the Manage EXPLAIN Tables & Aliases (ADB2EM1) panel, specify UPGRADE in the Action field:

   ADB2EM1 n           DD1A Manage EXPLAIN Tables & Aliases     Row 1 to 12 of 23 
   Command ===>                                                  Scroll ===> PAGE 
                                                                                  
   Commands: Run  Preview  SelAll   UnselAll   SelDiag   Options                  
   Line commands: S - Select  U - Unselect  M - Show message  T - Table           
                  DT - Drop table                                                 
   Input for ADMIN_EXPLAIN_MAINT stored procedure:                                
    Action . . UPGRADE (Create,Upgrade,Dropall)  Manage alias . . NO  (Yes/No)    
    Schema . . TS6462                                                           > 
   LC S W EXPLAIN Table              Schema         Owner                         
          *                          *              *                             
   -- - - -------------------------- -------------> ------------->                
      S   PLAN_TABLE                 TS6462         TS6462                        
      S   DSN_STATEMNT_TABLE         TS6462         TS6462                        
      S   DSN_STATEMENT_CACHE_TABLE  TS6462         TS6462                        
          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                                    

   Tips:

   • If the W (warning) column contains a value (I, W, or E), a pending message exists. To display the message, issue the M line command for that row.
   • You can change the Schema field value if needed. Enter ? to look up possible values.
4. Select the EXPLAIN tables that you want to upgrade by using the S line command against individual tables, the SELDIAG command (to select the diagnostic EXPLAIN tables), or the SELALL command (to select all EXPLAIN tables).

   If you select a table that does not yet exist, it will be created during the upgrade process.

   All tables that you want to upgrade should have either an U (upgrade) or S (selected) in the S column. U indicates that the table is to be upgraded but was not explicitly selected by the user. S indicates that the table was explicitly selected by the user for the action, which in this case is upgrade.

5. Optional: If you want to specify other values for theses tables, such as the database, storage groups, or buffer pools, instead of using the default values, complete the following steps:
   1. Issue the OPTIONS (or abbreviated O) command.
   2. On the Options for creating EXPLAIN tables (ADB2EMO) panel, specify the values that you want to use for your EXPLAIN tables:

      ADB2EMO n             DD1A Options for creating EXPLAIN tables            17:52
      Command ===>                                                  Scroll ===> PAGE 
                                                                                     
      Owner  . . . . . . . . . . TS6462                                             >
      For the following fields, enter ? for suggested values:                        
       Database  . . . . . . . .              Stogroup for database . .             >
       Index buffer pool . . . .              Stogroup for index  . . .             >
       4K  buffer pool . . . . .              8K  buffer pool . . . . .              
       16K buffer pool . . . . .              32K buffer pool . . . . .              
       For LOB:                                                                      
       4K  buffer pool . . . . .              8K  buffer pool . . . . .              
       16K buffer pool . . . . .              32K buffer pool . . . . .              

      Note: If you enter ? to look up buffer pools, the list is obtained by using the Db2 stored procedure ADMIN_COMMAND_DB2 to list buffer pools with VPSIZE greater than 0. Thus, not all configurable buffer pool names will be listed in the selection list.

      For any values that are not specified, the default values are used, as described in ADMIN_EXPLAIN_MAINT stored procedure (Db2 13 for z/OS documentation) .

   3. Press PF3 (END) to return to the Manage EXPLAIN Tables & Aliases (ADB2EM1) panel.
6. Optional: If you want to create a new alias, complete the following steps:
   Note: If you want to create the existing PLAN_TABLE aliases on the other selected EXPLAIN tables, this step (6) is not necessary. ADMIN_EXPLAIN_MAINT will create the PLAN_TABLE aliases on those tables when it performs the upgrade.
   1. Set the Manage alias field to YES.
      Any alias information and commands are displayed:

      ADB2EM1 n           DD1A Manage EXPLAIN Tables & Aliases     Row 1 to 12 of 23 
      Command ===>                                                  Scroll ===> PAGE 
                                                                                     
      Commands: Run  Preview  SelAll   UnselAll   SelDiag   Options  DropallAlias    
      Line commands: S - Select  U - Unselect  M - Show message  T - Table           
                     DT - Drop table  A - Alias  CA - Create alias  DA - Drop alias  
      Input for ADMIN_EXPLAIN_MAINT stored procedure: 
       Action . . UPGRADE (Create,Upgrade,Dropall)  Manage alias . . YES (Yes/No)    
       Schema . . TS6462                          > Schema alias . .               > 
      LC S W EXPLAIN Table              Schema         Owner          Schema Alias   
             *                          *              *              *              
      -- - - -------------------------- -------------> -------------> ------------->
         U   PLAN_TABLE                 TS6462         TS6462         Not found       
         U   DSN_STATEMNT_TABLE         TS6462         TS6462         Not found       
         U   DSN_STATEMENT_CACHE_TABLE  TS6462         TS6462         Not found           
      ...  

      When the Schema alias field is blank, the values displayed in the Schema Alias column are based on the aliases defined for schema.PLAN_TABLE. All existing PLAN_TABLE aliases are listed. If any of those aliases are also defined on other EXPLAIN tables, those aliases are also listed for those tables. The column does not list aliases that are defined only on other EXPLAIN tables and not on PLAN_TABLE.

      When the Schema alias field is populated, the Schema Alias column is filtered according to that value; only matching aliases are displayed.

   2. Specify a new alias in the Schema alias field.
      Tip: You can use ? in the Schema alias field to look up possible values.

      The alias will be created with the same name as the table. Therefore, the alias schema must be different than the table schema.

   3. Issue the CA line command next to the EXPLAIN table for which you want to create the alias.

      If you want to create the aliases for all selected and existing EXPLAIN tables, issue the CA line command next to PLAN_TABLE. If you create the alias on PLAN_TABLE, it will be created for the other selected EXPLAIN tables and existing EXPLAIN tables with the same schema as PLAN_TABLE when Db2 Admin Tool calls ADMIN_EXPLAIN_MAINT to perform the upgrade.

      Tip: If you want to see all existing aliases again, clear the Schema alias field.
   4. Repeat steps 6.b and 6.c as needed to create as many aliases as you want.
7. Optional: If you want to preview the action first, issue the PREVIEW (or P) command.
   The ADMIN_EXPLAIN_MAINT stored procedure is run in preview mode, and the results are displayed on the Execution Results (ADB2EM2) panel. Use the S line command to view the result sets.
8. Issue the RUN (or R) command.

   If any aliases need to be created on PLAN_TABLE before calling ADMIN_EXPLAIN_MAINT, the CREATE ALIAS statements are executed at this time.

   Then, the results from calling the ADMIN_EXPLAIN_MAINT procedure are displayed:

   ADB2EM2 n        DD1A Execution Results                        Row 1 to 2 of 2 
   Command ===>                                                  Scroll ===> PAGE 
   
   Execution result of UPGRADE:                                                   
   Return code  : 0                                                               
   Error message:                                                            >    
   Line commands: S - Show result set                                             
   L Result set                                                                   
   - ----------------------------------------                                     
     SYSIBM.EXPLAIN_MAINT_SUMMARY                                                 
     SYSIBM.EXPLAIN_MAINT_SQL                                                     
   ******************************* END OF DB2 DATA *******************************

9. Issue the S line command against the result sets to view more details about the objects that were created or upgraded.
10. Exit (PF3) the results panel.

    The Manage EXPLAIN Tables & Aliases (ADB2EM1) panel lists the schemas, owners, and any aliases for any tables that were created or upgraded.