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

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

Important

File-AID does NOT create indexes on the explain tables. For Db2 Version 10 and 11, IBM recommends to create an index on the Db2 Version 10 and 11 sample PLAN_TABLE, if using user-level and/or instance-level optimization hints. Refer to sample CREATE TABLE statements in member DSNTESC of the SDSNSAMP library.

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

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

Important

To update the added EXPLAIN_TIME columns, select Option 4.04 (see 4, Generate UPDATE EXPLAIN_TIME JCL).

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

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

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

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.