Accessing the SQL Analysis function

To access the SQL Analysis function, select option 4 on the File-AID for Db2 Primary Option menu. The SQL Analysis and Development Menu (see the following figure) displays. Select the option to be used.

SQL Analysis and Development Menu

image2021-3-11_17-18-35.png

Important

  1. SQL Analysis only supports Db2 Version 10 and above.
  2. If your Explain tables were created prior to Release 10.2 of File-AID for Db2, we recommend to upgrade your Explain tables using Option 4.0.2.1 Upgrade Plan Table and to update the EXPLAIN_TIME columns (see 4, Generate UPDATE EXPLAIN_TIME JCL) before selecting any other SQL Analysis option.
  3. When selecting Option 1, 2, or 3 and your Explain tables have NOT been upgraded, the Explain Table Warning window (Explain Table Warning) displays with the message that an Explain table upgrade is required for your PLAN_TABLE and/or F2PLAN_TABLE.

Explain Table Warning

image2021-3-11_17-19-25.png

Press Enter to upgrade the Explain table(s) and continue to your selected option.

Pressing END will cancel the ALTER(s) from being executed. This will result in errors attempting to perform SQL Analysis. The ALTER statements will need to be executed in order for File-AID for Db2 SQL Analysis to complete successfully.

When you press Enter in the Explain Table Warning window (Explain Table Warning), File-AID for Db2 will upgrade the Explain table(s) and will then also generate batch JCL that will execute Db2 UPDATE statements against your PLAN_TABLE and/or F2PLAN_TABLE rows. 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.
  • In case you didn’t submit the generated batch JCL after upgrading the Explain table(s), select Option 4.0.4 Update EXPLAIN_TIME columns to regenerate this job (see 4, Generate UPDATE EXPLAIN_TIME JCL).
  • The default output for the UPDATE EXPLAIN_TIME columns job is written to F2SYSOUT DD SYSOUT=*,HOLD=YES. You may choose to write the output from the job to a data set by:
    1. Modifying the PRINTOPT Y control card on the SYSTSIN DD * to PRINTOPT N.
    2. Modifying the PRDSNAME control card on the SYSTSIN DD * to specify a data set name inside the single quotes, for example PRDSNAME 'your.dataset.name.here'.

OPTION

Enter the desired option in the SQL Analysis and Development Menu (SQL Analysis and Development Menu).

0

Displays the Create or Maintain Explain Table screen (Create-or-Maintain-Explain-Table-s).

1

Displays the SQL Plan Analysis Specification screen (SQL-plan-analysis).

2

Displays the SQL Explain Report Specification screen (Review-or-print-Db2-explain-reports).

3

Displays ISPF/PDF SQL Source Development and Analysis screen (ISPF-PDF-SQL-source-development-and-analysis).

L

Initiates CA Librarian SQL Source Development and Analysis (see LIBRARIAN-source-development-and-analysis). If you do not have CA Librarian, this option is not available. Contact the person who installed File-AID for Db2 if you think this option should be available.

P

Initiates CA Panvalet SQL Source Development and Analysis (see PANVALET-source-development-and-analysis). If you do not have CA Panvalet, this option is not available. Contact the person who installed File-AID for Db2 if you think this option should be available.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*