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.
Before you begin
About this task
Procedure
To upgrade EXPLAIN tables:
- On the DB2 Administration Menu (ADB2) panel, specify option E, and press Enter.
-
On the EXPLAIN (ADB2E)
panel, specify option
MNT or M (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 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.
-
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) orS
(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. - 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:
- Issue the OPTIONS (or abbreviated O) command.
-
On the Options for creating EXPLAIN
tables (ADB2EMO) panel, specify the
values that you want to use for your EXPLAIN tables:
Figure 3. Options for creating EXPLAIN tables (ADB2EMO) panel ADB2EMO n DD1A Options for creating EXPLAIN tables 17:52 Command ===> Scroll ===> PAGE Authid . . . . . . . . . . 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) .
- Press PF3 (END) to return to the Manage EXPLAIN Tables & Aliases (ADB2EM1) panel.
- 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.
-
Set the Manage alias field to
YES.
Any alias information and commands are displayed:
Figure 4. 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 ...
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 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.
-
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 6.b and 6.c as needed to create as many aliases as you want.
-
Set the Manage alias field to
YES.
- 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.
-
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:
Figure 5. Execution Results (ADB2EM2) panel 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 *******************************
- Issue the S line command against the result sets to view more details about the objects that were created or upgraded.
-
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.