You can save SQL statements to the SQL Repository from other modules such as the SQL Scanner and SQL Optimizer.
Click Save SQL to SQL Repository .
Select the location in which to save the SQL statements
Note: If you are using this function from the Job Manager window you need to select which Job to be added first. Only valid SQL statements are saved to the SQL Repository.
The Generate Indexes function analyzes the syntax of your original SQL statement and the database structure and then proposes new index candidates to help improve performance. The SQL statement can be executed using the index recommendations to identify which index yields the greatest performance gain.
Click to open a SQL Optimizer session.
On the SQL tab, enter the SQL statement for which you want to analyze for index alternatives.
Note: To copy a SQL statement from other windows such as Scanned SQL Viewer, SQL Formatter, Database Explorer, or SQL Comparer, click .
Click to view the current access plan for the SQL.
Click to generate alternative index sets.
All index-set alternatives that Index Expert generates are listed in the in the Run Time pane. Index sets identified by the Index Expert Artificial Intelligence engine are labeled Setx; those recommended by DB2 are labeled DB2 LUW.
Tips:
To obtain actual run-time statistics when the SQL statement when it uses each index-set scenario, click , select your benchmarking options, and click OK.
The Batch Run window opens, enabling you to view the run-time results as each scenario executes.
Important Note: This benchmark process may impact the performance of SQL statements executing on your database server.
When Batch Run is finished, use the Run Time pane view the results for each index-set alternative.
To analyze the impact of the index-set alternatives on the access plans of other SQL statements, click to run Impact Analysis.
Tip: To analyze the impact of virtual index sets on other SQL in the database, click . See Analyze the Impact of New Indexes for more information.
The Index Impact Analyzer allows you to analyze the impact of new indexes on the SQL statements in your database.
Click .
Click . If this is your first time in the Index Impact Analyzer, the New Analysis window automatically opens.
Under the Analyzer tab in the New Analysis window, select to check the effects of index creation by either Creating a new Analyzer or Continuing an existing Analyzer.
Enter a name and description.
If you would like to create a folder, click .
Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.
Select the SQL statement(s) to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking .
Under the SQL Access Plans will be analyzed section, select the options for retrieving the access plans.
Using existing access plan saved with the SQL: This option uses the access plan that was saved with the SQL statement at the time that statement was saved to the SQL Repository, or scanned in the SQL Scanner.
Obtaining a new access plan under the current connection: This option retrieves the access plan with the current database logon. This current access plan is compared to the access plan that is retrieved after creating the new index(es).
Name the Index Scenario and enter an optional description for easy reference.
If the Index Impact Analyzer was called from the Index Expert, the index scenarios are automatically display and you can choose the index scenarios to include for analysis.
To create the indexes, click .
Give the index a name for easy reference.
In the ‘Index based on’ section of this window, specify in which schema to create this index and specify the table that contains the column(s) to include in the index.
The columns of the table appear in the ‘Table columns’ window and can be selected to use in the index scenario, under the ‘Index columns’ window, by double clicking them or using the right arrow button.
Specify the Index type by selecting the option to have the index Unique and whether to Allow Reverse Scans.
Click OK to perform the Index Impact Analysis.
After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.
In the right pane for the scenarios, click the Prognosis button to see overall performance changes and SQL details.
The Index Usage Analyzer identifies the usage of the indexes in your database application by uncovering both the indexes that are being used and the indexes that are not being unused. It analyzes the access plans from SQL statements in the SQL statements that are save in the SQL Repository. You can use this module to quickly identify the indexes in databases that are not contributing to the performance of the database applications. These unused indexes can then be deleted to free up space and improve the speed of the database applications and maintenance.
Click .
Click . If this is your first time in the Index Usage Analyzer, the New Analysis window automatically opens.
Give the analysis a name and description for easy reference.
If you would like to create a folder, click .
Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.
Select the SQL statements to add to the Analysis from your predefined SQL statements, or you may add a statement to this Analysis by clicking .
Click OK.
After the analysis, you can see the overall results by clicking the Analyzer, and its related information, in the tree structure in the left pane and viewing the corresponding information in the panes to the right.
In the right pane, click the Index Summary button to see a list of indexes used and unused.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Términos de uso Privacidad Cookie Preference Center