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
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 procedureNote: 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 foundTips:- 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 foundWhen 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.