SQL Optimizer for DB2 LUW 4.4 - User Guide

SQL Repository Tutorial

The SQL Repository stores the SQL statements that are used in the analysis of database performance. These may be SQL statements that you have identified as critical to the performance of your database application.

Adding SQL to the SQL Repository

1. Click .

2. If no SQL exists in the SQL Repository, then the Add SQL window appears automatically. Otherwise, you can open the Add SQL window by clicking button.

3. In the Add SQL window enter the SQL text in the SQL Information tab. Click OK.

4. The SQL syntax is checked and the access plan retrieved before adding a new node to the SQL tree view with the SQL name. Each SQL statement added to the SQL Repository contains an access plan, SQL classification type (Simple, Complex or Problematic), and the current connection information (login name, database alias, and schema). The access plan stored with the SQL statement is important as it indicates the current performance of the SQL.

Related Topics

SQL Repository Overview

SQL Repository Window

SQL Repository Functions

Saving SQL to the SQL Repository from Other Modules Tutorial

Save SQL to the SQL Repository from Other Modules Tutorial

You can save SQL statements to the SQL Repository from other modules such as the SQL Scanner and SQL Optimizer.

1. Click Save SQL to SQL Repository .

2. Select the location in which to save the SQL statements

3. Click OK.

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.

Related Topics

SQL Repository Tutorial

SQL Repository Overview

Save SQL to SQL Repository

Generate Virtual Indexes Tutorial

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.

Create index-set candidates

1. Click to open a SQL Optimizer session.

2. 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 .

3. Click to view the current access plan for the SQL.

4. 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:

• In the Run Time pane, index-set alternatives are listed along with any SQL alternatives (which you generate by clicking ). In this way, you can easily compare the performance of the index-sets with each other and the SQL alternatives.
• For the currently selected index-set alternative, view the its virtual DDL in the SQL Text pane. Use the SQL Information pane to view the index's virtual access plan and the optimized SQL that uses this plan.
• Create you own virtual index sets by clicking .

Test index sets

5. 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.

6. When Batch Run is finished, use the Run Time pane view the results for each index-set alternative.

7. 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.

Related Topics

Generate Index-Set Alternatives

Index Impact Analyzer Tutorial

The Index Impact Analyzer allows you to analyze the impact of new indexes on the SQL statements in your database.

1. Click .

2. Click . If this is your first time in the Index Impact Analyzer, the New Analysis window automatically opens.

Analyzer Tab

3. 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.

4. Enter a name and description.

5. If you would like to create a folder, click .

Select SQL Tab

6. Select the source of the SQL statements for Analysis: SQL Repository or SQL Scanner.

7. 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 .

8. 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).

Index Tab

9. Name the Index Scenario and enter an optional description for easy reference.

10. 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.

11. 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.

12. Click OK to perform the Index Impact Analysis.

Reviewing Index Impact Analysis Results

13. 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.

14. In the right pane for the scenarios, click the Prognosis button to see overall performance changes and SQL details.

Related Topics

Index Impact Analyzer Overview

Index Impact Analyzer Window

Index Impact Analyzer Functions