SQL Optimizer for DB2 LUW 4.3.1 - User Guide

Stop Recording Activities

To stop recording activities in the Activity Log

1. Click .

2. Select the Activity Log tab.

3. In the Activity to be logged section, clear both the SQL optimization and Access plan generation checkboxes to disable the Activity Log.

Tutorials

SQL Scanner Tutorial

Use the SQL Scanner to analyze SQL statements embedded within database objects, DB2 Event Monitor files/tables, text/binary files and application source code. The SQL Scanner extracts each SQL statement embedded within the scanned objects or files, retrieves their respective access plans from DB2 LUW, and performs an analysis that determines which of these SQL statements may be performance bottlenecks. You can examine the extracted SQL statements with their access plans and then, copy the SQL statements identified as problematic (top priority) or complex (second priority) into the SQL Optimizer or the Index Expert.

Open a Scanner Group

1. Click .

2. When you scan the database objects or the application files, you first create a Group to store the items you want to scan. If this is the first time you have used the SQL Scanner, the Create Group window appears. Otherwise, click Create in the Group Manager window.

3. Enter a new Group name, e.g. "Test." Click OK to close the Create Group window.

4. Check that your new Group name is highlighted in the list box. Click Open.

Add Scanner Jobs

5. The selected group is opened in the Job Manager window. For a new Group, the Add Jobs wizard automatically opened so you can select what files or database objects you want to scan.

Note: If you are using an existing Group, click .

6. In the Add Jobs wizard, click the Next button until you are at the page for the item that you want to scan.

Database Objects page

1. Expand the database user branch on the left side of the window.

2. Highlight the schema, a database object type, or an individual database object, and click to move the item to the right pane. (Whether or not you can scan all of the selected database objects depends on your database privileges.)

DB2 Event Monitor

1. From the left pane, select the Event Monitor.

2. Click to move the Event Monitor to the right pane.

3. Set the schema in the Specify Schema list to correspond with the SQL that you are scanning.

Source Code page

1. Click the Text or binary files or COBOL programming source code option.

2. Click and select the files you want to scan.

3. Click Open to insert the files in the Add Jobs wizard.

4. Set the schema in the Schema list to correspond with the SQL that you are scanning.

Summary page

1. Review the jobs you selected.

2. Click Finish.

Scan Jobs

7. Click Scan .

8. Review the details that are filled in the Job Manager grid as the scanning process completes each job. It will show you how many SQL statements found in the Job and how each SQL statement is classified.

View scanning results

9. To view the scanned SQL statements, highlight the item by clicking the row and click .

10. The name of the source file or database object appears at the top of the window in the Job list.

11. The first SQL statement found is shown in the left pane. Click the tabs, e.g. SQL1, SQL2, SQL3, etc., at the bottom left of the window to view the other SQL statements.

12. Notice the buttons on the top of the right pane. These buttons display in the right pane the access plan, the SQL classification and connection information, the DDL for temporary tables used by the SQL, and details about SQL statements that you have reviewed.

13. You can narrow the number of SQL statements to view only the problematic and/or complex statements with View | Problematic SQL and/or View | Complex SQL.

14. Select one SQL statement you want to analyze for performance improvement. Click to copy the SQL statement to the SQL Optimizer window and start the optimization process. Alternatively, you can also have index option generated for the SQL. Click to copy the SQL statement to the Index Expert window and generate index options.

Related Topics

SQL Scanner Overview

Job Manager Overview

Job Manager Window

Job Manager Functions

Scanned SQL Viewer Overview

Scanned SQL Viewer Window

Scanned SQL Viewer Functions

SQL Optimizer Tutorial

SQL optimization is a four-phase process:

• The SQL Rewrite step creates virtual alternative SQL statements, retrieves their respective access plans from DB2 LUW, and indicates each statement’s DB2 LUW cost. These alternatives produce the same results as your original SQL statement, but each has a different access plan.
• The Generate Indexes step creates virtual index sets and adds these as alternatives.
• The next phase test-runs your original SQL statement and any or all of the alternatives against your database to obtain run times.
• The last phase is determining the SQL or index-set alternative that performs best.

Tip: The DB2 LUW Cost is only an estimate of the resources it takes to execute a SQL statement. It is essential to run Batch Execute on the alternatives to determine which statement actually performs the best.

Rewrite the SQL statement

1. Click to open a SQL Optimizer session.

2. On the SQL tab, enter the SQL statement you want to optimize.

3. Click . This step launches the SQL Rewrite process that automatically transforms the syntax of the SQL statement.

   Notes:

   • The use of SQL Options and other optimization options such as temp table generation, ANSI JOIN syntax are optional and configurable in the Options.
   • The degree of the SQL transformation process is controlled by the Intelligence Level in the Options. The Intelligence Levels control how many options are applied to transformed SQL and how many SQL alternatives are created.
   • If your SQL statement uses a temporary table, see the section User-Defined Temp Table for the steps to create a temporary table in the User-Defined Temp Table module.

   After the rewrite, the Rewrite Details dialog shows the total number of semantically equivalent SQL statements, the number of alternative statements with access plans different from your original statement, and a warning message if the number of SQL transformations reaches any of the optimization quotas set in the Options.

4. Click Close.

   The SQL Optimizer window shows several tabs that provide information about the original SQL statement and its SQL alternatives. The SQL tab displays the SQL text and the access plan of the currently selected SQL alternative (or the original SQL statement). At the bottom of the tab is the Run Time pane. This pane lists the original SQL statement, the SQL alternatives, and the run-time statistics for all of these after they are executed. At this point, since you have not yet run Batch Execute, the pane shows displays only DB2 LUW cost value for the original SQL statement and each SQL alternative.

5. In the Run Time pane, select an SQL alternative.

6. Go to the Access Plan tab to view the alternative's access plan and statistics for the objects accessed by the SQL statement.

7. To see how the syntax of an alternative SQL statement differs from that of your original SQL, do the following:

   1. Go to the Compare tab.

      Your original SQL statement is displayed in one pane of the page and the alternative statement in the other pane. Blue highlighted shows differences in the SQL syntax.

   2. Click , , or to customize what is displayed on the page.

8. Go to the Plan Statistics tab to compare the cost estimates between the original SQL access plan and the plan for each alternative.

9. Go back to the SQL tab.

Create index-set candidates

10. In the SQL Optimizer window, click to generate virtual index sets. These index sets include those that the SQL Optimizer's Index Expert component recommends and those that DB2 recommends.

    Note: If you are connected toDB2 LUW 8 or later, Index Expert generates its own virtual index sets and optionally includes indexes that DB2 recommends. If you are using DB2 LUW 7, only DB2-recommended index sets are retrieved.

    The resulting virtual index-sets display as alternatives in the in the Run Time pane. Index-set alternatives recommended by Index Expert are labeled Setx; those recommended by DB2 are labeled DB2 LUW.

Batch test SQL alternatives

11. To prepare to execute the original SQL, SQL alternatives, and index-set alternatives, click .

12. In the Batch Run Criteria window, select the Selected SQL/Index Set tab.

    1. Select which alternatives to execute. The blue checkmark in the left column indicates that the alternative is selected. By default, all alternatives are selected.

    2. To unselect a statement, right-click the alternative and select the appropriate option.

13. Select the defaults on the remaining tabs in the Batch Criteria window, and click OK.

    Note: If you need to make edits to any criteria, see Retrieve the Run Time for a Group of Alternatives for more information.

    The Batch Run window opens enabling you to view the results as each statement executes.

14. When all the selected SQL statements have finished executing, the Batch Run Details window appears. Click OK.

Review test results

17. In the Run Time pane on the SQL tab, review the columns that contain various types of execution time.

18. Once you have identified the most-efficient alternative SQL statement, you can any of the following:

    • Copy and paste it back in your application.

    • Save the alternative SQL statement in a text file either individually or with multiple SQL statements in the Optimized SQL report.

    • Save your SQL optimization results for later review. Select SQL | Saved Optimized SQL.

Related Topics

SQL Optimizer Window

SQL Optimizer Functions

Automatically Rewrite the Original SQL Statement

Generate Index-Set Alternatives