Create or Maintain Explain Table(s)
On this screen you can create Plan, Statement, and Function tables, as well as maintain and delete rows from them.
Select option 0 from the SQL Analysis and Development menu. The Create or Maintain Explain Table(s) screen displays (see the following figure).
Create or Maintain Explain Table(s) Screen
OPTION ===> Scroll ===> PAGE
SSID: DSN
Plan_Table AUTHID ===> USERID Authorization ID for all EXPLAIN tables
1 Create Explain Table(s)
2 Alter or Upgrade Explain Table(s)
3 Delete rows from Explain Table(s)
4 Generate UPDATE EXPLAIN_TIME JCL
(Explain Tables: PLAN_TABLE, F2PLAN_TABLE, DSN_STATEMENT_TABLE,
DSN_FUNCTION_TABLE)
1, Create PLAN_TABLE, DSN_STATEMNT_TABLE, DSN_FUNCTION_TABLE
Select option 1 from the Create or Maintain Explain Table(s) screen. The Create Explain Table(s) screen displays (see the following figure).
On this screen you can create the Plan table, as well as the Statement and Function tables. The Plan and F2Plan tables are required. You must explicitly specify all database names.
Wildcards are not valid on this screen.
Create Explain Table(s) screen
COMMAND ===> Scroll ===> CSR
SSID: DSN
Select the tables below to be created.
Plan_Table AUTHID ===> USERID Authorization ID for all EXPLAIN tables
Required Tables:
Specify the Database and Tablespace for PLAN_TABLE
Database ===> USERIDDB
Tablespace ===> USERIDTS (blank for implicit Tablespace)
Specify the Database and Tablespace for F2PLAN_TABLE
Database ===> USERIDDB
Tablespace ===> USERIDTS (blank for implicit Tablespace)
Optional Tables:
_ Specify the Database and Tablespace for DSN_STATEMENT_TABLE
Database ===> USERIDDB
Tablespace ===> (blank for implicit Tablespace)
_ Specify the Database and Tablespace for DSN_FUNCTION_TABLE
Database ===> USERIDDB (This table requires at least a 8K
Tablespace ===> buffer pool to be created in DB2 V8)
Instructions:
Press ENTER to continue or END to cancel.
2, Alter or Upgrade PLAN_TABLE
Select option 2 from the Create or Maintain Explain Table(s) screen. The SQL Plan Analysis Specification Screen displays (see Alter Plan Table Screen).
On this screen you can upgrade your plan table from an older version of Db2 to a more current version, and also drop/recreate your plan table and F2 plan table. The example shows upgrading to Db2 Version 11.
Alter Plan Table Screen
COMMAND ===> Scroll ===> CSR
SSID: DSN
Plan_Table AUTHID ===> USERID Authorization ID for all EXPLAIN tables
1 Upgrade EXPLAIN tables to DB2 Version 11
2 Drop Existing EXPLAIN tables and recreate them in DB2 Version 11
Upgrade Plan Table
Select 1 from the Alter Plan Table screen. The Upgrade Plan Table screen displays (Upgrade Plan Table Screen).
The Upgrade Plan Table screen shows all the changes to be done to the plan table you are upgrading, based on the version you request. Upgrade Plan Table Screen shows an example of upgrading from Db2 Version 9 to Db2 Version 11.
Any changes made to the plan table also are made to the F2 plan table.
You cannot change anything on this screen - all fields are protected and for information only.
Upgrade Plan Table Screen
COMMAND ===>
Sqlid: USERID1
to V11
Alter PLAN_TABLE
Add Columns:
BIND_EXPLAIN_ONLY CHAR(1) NOT NULL WITH DEFAULT (V10)
SECTNO INTEGER NOT NULL WITH DEFAULT (V10)
EXPLAIN_TIME TIMESTAMP NOT NULL WITH DEFAULT (V10)
MERGC CHAR(1) NOT NULL WITH DEFAULT (V10)
MERGN CHAR(1) NOT NULL WITH DEFAULT (V10)
SCAN_DIRECTION CHAR(1) (V11)
EXPANSION_REASON CHAR(2) NOT NULL WITH DEFAULT (V11)
Press ENTER to continue or END to cancel.
Drop and Recreate Existing EXPLAIN Tables
Select 2 to drop and recreate the existing explain tables. In the Confirm Drop Explain Tables window (see the following figure), press Enter to drop the tables, or END (PF3) to cancel.
Confirm Drop Explain Tables Window
After you confirm dropping the existing Explain Tables, enter the database name and, optionally, a tablespace name for the tables to be created or re created in the Create Explain Table(s) screen.
3, Delete Rows from Explain Table
Select option 3 to perform maintenance on the plan, statement and function tables. The Explain Table(s) Maintenance Specification screen displays. On this screen you can delete obsolete plan, statement, and function table information. This function should be performed frequently when File-AID for Db2 Explains are used extensively.
Performance of SQL and Plan analysis features are negatively affected when the plan table and alternate table become too large. In addition, this function’s performance is negatively affected when you delete on a row by row basis; that is, when you enter YES in the Preview Rows Prior to Delete field.
Explain Table(s) Maintenance Specification Screen
COMMAND ===> SCROLL ===> CSR
Plan_Table AUTHID ===> FDDBA Authorization ID of Explain Tables
Specify Processing Options:
Preview rows prior to delete ===> YES (Yes or No)
Explain_Table rows to delete ===> FD (Any or FD) * See note below *
Specify the range of Explain dates/times for delete:
Starting Date ===> 2009-05-14 Starting Time ===> 00.00.01
Ending Date ===> 2009-05-14 Ending Time ===> 15.26.31
Note: This function will delete rows from the DB2 AUTHID.PLAN_TABLE,
AUTHID.F2PLAN_TABLE (only used by File-Aid for DB2),
AUTHID.STATEMENT_TABLE and AUTHID.FUNCTION_TABLE.
If you specify FD for Rows to delete, only Explain Table rows created
thru File-AID for DB2 will be deleted. These also, are the only rows
available for preview before deleting.
If you specify ANY with preview NO, qualified row created inside
and outside of File-AID for DB2 will be deleted.
Explain_Table_AUTHID
Enter the creator ID or authorization ID of the selected table(s).
Preview Rows Prior to Delete
Specify whether the SQL Explain Tables Row Deletion screen displays.
YES | SQL Explain Tables Row Deletion screen displays, which lists those rows created by File-AID for Db2 and meet the specified criteria. From this screen, you can select which rows to delete, or which rows to provide more detail. The plan, statement and function table rows generated outside of File-AID for Db2 are not listed or deleted by this function. |
NO | SQL Explain Tables Row Deletion screen is not displayed. The deletion process occurs immediately upon pressing Enter when NO is specified. All rows which qualify based on the criteria specified are deleted. |
Explain_Table rows to delete
Enter ANY or FD. FD specifies to delete Explain Table rows that were created with File-AID for Db2. ANY specifies to delete Explain Table rows created within and outside of File-AID for Db2. Deletes rows from the Db2 AUTHID.PLAN_TABLE, AUTHID.F2PLAN_TABLE, AUTHID.STATEMNT_TABLE and
AUTHID. FUNCTION_TABLE.
Starting Date/Ending Date
Enter the range of dates for all Explain Table rows to be deleted. Only those within the date range will be deleted. This is a required field. It contains the current system date as a default.
Starting Time/Ending Time
Enter the range of time for all Explain Table rows to be deleted. Only those within the time range will be deleted. This is a required field. It contains a time one second past midnight as a default.
Row Deletion
Enter YES on the Preview Rows Prior to Delete field on the Explain Table(s) Maintenance Specification screen. The SQL Explain Tables Row Deletion screen displays. On this screen you can delete individual rows using the D line command. Enter an S in the CMD field to display the Maintenance Detail screen.
SQL Explain Tables Row Deletion Screen
COMMAND ===> SCROLL ===> CSR
SSID: DSN
The following rows from the F2PLAN_TABLE will be used as criteria
for selecting rows from the Explain tables to be processed.
Line Commands:
S - Select row for more detail
D - Delete the row
CMD TIMESTAMP STMTNO SQL ORIGIN
_ 2009060211025667 1 <SOURC>
*************************** Bottom of data ***************************
CMD
Specify the line command to be performed on the selected row.
S | Displays the Maintenance Detail screen. Different screens appear depending on whether you are selecting rows from the plan table, function table, or statement table. Maintenance Detail screens allow you to review the contents of the row prior to its deletion. |
D | Deletes a row from the plan table. The message Delete Pending displays in the upper right corner of the screen. The D remains in the CMD field until END is pressed, at which time the row is deleted from the plan table. To remove a D, space over it. |
Maintenance Detail
The Maintenance Detail screen is displayed when you select a statement on the SQL Plan Table Row Deletion screen. This screen allows you to review individual rows prior to deleting them. The SQL statement displays in the upper section of the screen and each referenced table is listed in the lower portion (Plan Table only). The list of referenced table(s) can be scrolled (DOWN/UP) if needed. Enter the END command to return to the Row Deletion screen.
Maintenance Detail Screen
COMMAND ===> SCROLL ===> CSR
SSID: DSN
SQL Statement:
DECLARE PLAN31_CSR2 CURSOR FOR
SELECT NAME, CREATOR, BINDDATE, VALIDATE, ISOLATION, VALID,
OPERATIVE, BINDTIME, PLSIZE, IBMRLOD, AVGSIZE, ACQUIRE, RELEASE,
EXPLAN, BOUNDRY, QUALIFIER, CACHESIZE, PLENTRIES, DEFERPREP,
CURRENTSERVER, SYSENTRIES, DEGREE, SQLRULES, DISCONNECT
FROM SYSIBM.SYSPLAN
WHERE CREATOR = :SQLCCREA
AND NAME LIKE :SQLCPPLN FOR FETCH ONLY
Qblk Plan Access Match Index TS SortN SortC Table Pre Col Mix
No No Method Type Cols Only Lock UJOG UJOG No Fetch Eval Seq
1 1 0 I 1 N IS NNNN NNNN 1 0
Access Access Join Join Table: SYSIBM.SYSPLAN
Degree Pgroup ID Degree Pgroup ID Index: SYSIBM.DSNPPHO1
<NULL> <NULL> <NULL> <NULL>
4, Generate UPDATE EXPLAIN_TIME JCL
Select this option when you want to generate the job that executes Db2 UPDATE statements against your PLAN_TABLE and/or F2PLAN_TABLE rows to update the EXPLAIN_TIME columns after you upgraded your Explain Tables. See also 2, Alter or Upgrade PLAN_TABLE and Explain Table Warning window (Explain Table Warning). Review the JCL, adjust to your site’s requirements, and submit the job.
- PLAN_TABLE rows will not be viewable in Review-or-print-Db2-explain-reports until this job completes successfully.
- If your PLAN_TABLE or F2PLAN_TABLE contains a large number of Explain rows, this job may require additional time to finish execution.
Related topics