Creating aliases for EXPLAIN tables

An alias allows a user with SELECT and INSERT privileges to populate EXPLAIN tables that are created under a different AUTH ID.

Before you begin

You must have the following Db2 APARs applied:

Also, this procedure assumes that the EXPLAIN tables for which you want to create aliases already exist. If they do not already exist, you can create the tables and aliases at the same time. See Creating EXPLAIN tables.

Procedure

To create aliases for EXPLAIN tables:

On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.

On the EXPLAIN (ADB2E) panel, specify option MNT orM (for maintenance), and press Enter.

Figure 1. EXPLAIN (ADB2E) panel

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.

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

Figure 2. Manage EXPLAIN Tables & Aliases (ADB2EM1) panel

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                  
   U   DSN_PREDICAT_TABLE         TS6462         TS6462      
       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.

Set the Manage alias field to YES.

Any alias information and commands are displayed:

Figure 3. Manage EXPLAIN Tables & Aliases (ADB2EM1) panel with schema alias

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      
        DSN_QUERYINFO_TABLE        Not found                     Not found    
    U   DSN_PREDICAT_TABLE         TS6462         TS6462         Not found      
        DSN_FILTER_TABLE           Not found                     Not found    
        DSN_DETCOST_TABLE          Not found                     Not found    
        DSN_COLDIST_TABLE          Not found                     Not found    
        DSN_KEYTGTDIST_TABLE       Not found                     Not found    
        DSN_VIRTUAL_INDEXES        Not found                     Not found    
        DSN_VIRTUAL_KEYTARGETS     Not found                     Not found    
        DSN_FUNCTION_TABLE         Not found                     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.

Specify the alias that you want to create in the Schema alias field and press Enter.

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.
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.
Repeat steps 5 and 6 as needed to create as many aliases as you want.
If you created the alias only on the PLAN_TABLE and want to propagate it to other tables, issue the RUN (or R) command.

The results from calling the ADMIN_EXPLAIN_MAINT procedure are displayed.