SQL Optimizer for DB2 ZOS 5.6 - User Guide

Delete Remaining Indexes

Delete Remaining Indexes window appears if it has been detected that some virtual/real indexes exist in the database and these might affect index searching result.

It is recommended to remove the unused indexes before starting the search. Click OK to remove the selected indexes.

Test Run SQL Alternatives

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

Use SQL Optimizer to perform test runs on alternatives in order to view their run-time statistics. You can test run a single statement, selected statements, or all statements. Use the Test Run Single SQL function to retrieve run-time statistics for a single statement.

Use the Test Run function to perform multiple test runs on selected or all statements. These functions allow you to get run-time statistics for selected SQL or average run-time statistics for all SQL.

Note: SQL Optimizer automatically rolls back any changes made to the database if the SQL is an INSERT, UPDATE, DELETE, or MERGE statement. This maintains the integrity of your data and provides that the initial data is the same for each SQL alternative so that the test is comparable.

Test Run Single SQL Function

Perform a test run on a single SQL statement run to get run-time statistics.

To test run a single statement

1. Select the Optimize tab.

2. Select a SQL statement in the Alternatives pane.

3. Click .

Test Run Function

To test run all statements

1. Select the Optimize tab.
2. Click the arrow beside and select Test Run - All.
3. Use the tabs to select test run criteria.

To test run selected statements

1. Select the Optimize tab.

2. In the Alternatives pane, select multiple SQL statements in one of the following ways:

• To select a consecutive group of SQL statements, click the first SQL, press and hold SHIFT, and then click the last SQL.
• To select non-consecutive SQL statements, press and hold CTRL, and then click each SQL that you want to select.

3. Click the arrow beside and select Test Run - Selected.
4. Use the tabs to select test run criteria.

Session Test Run Criteria

Every session can have its own test run settings. See the following topics for more information:

• Execution Method Options
• Order and Termination Options

Tips:

• You can change Test Run settings globally on the Test Run Options page. Test Run Settings Options
• Click to stop the test run process.
• You can select an executing SQL statement and click to abort only the selected statement.

Multiple Test Runs Function

To test run all statements multiple times

1. Select the Optimize tab.
2. Click the arrow beside and select Multiple Test Runs - All.

3. Enter the number of times you want each SQL statement executed.

To test run selected statements multiple times

1. Select the Optimize tab.

2. In the Alternatives pane, select multiple SQL statements in one of the following ways:

• To select a consecutive group of SQL statements, click the first SQL, press and hold SHIFT, and then click the last SQL.
• To select non-consecutive SQL statements, press and hold CTRL, and then click each SQL that you want to select.

3. Click the arrow beside and select Multiple Test Runs - Selected.
4. Enter the number of times you want each SQL statement executed..

Related Topics

Optimize SQL Statements

Automatically Optimize SQL Statements

Retrieve Run Results

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To retrieve run results

1. Select the Optimize SQL tab.
2. Enter a SQL statement in the Alternative Details pane of the SQL Details tab.
3. Right-click the Alternative Details or SQL Text pane and select Run Result.
4. Select the maximum number of rows to retrieve.

5. Click .

   Tip: Click to copy the SQL statement to execute in another Questproduct.

Create User Defined Alternatives

In addition to using SQL Optimizer to generate alternative statements, you can create user defined alternatives. You can create user defined alternatives using SQL text from your original SQL statement or from SQL text of an alternative SQL Optimizer generated.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To create a user defined alterative

1. Select the SQL Details tab in the main window.

2. Enter the SQL statement in the in the Alternative Details pane.

3. Click .

4. Edit the statement in the SQL Text pane to create a user defined alternative.

   Note: To create a user defined alternative using SQL text of an alternative SQL Optimizer generated, right-click an alternative and select User Alternative.

Caution: SQL Optimizer does not check that user defined alternatives are semantically equivalent to your original SQL statement. Review the execution statistics for user defined alternatives to compare the results with your original statement. See View Execution Statistics for more information.

Related Topics

Compare SQL Statements

Test Run SQL Alternatives