SQL Optimizer for DB2 LUW 4.4.1 - User Guide

Start Recording Activities

By default, the Activity Log is not created.

To start recording the activities during optimization and access plan retrieval

1. Click .

2. Select the Activity Log tab.

3. In Activity to be logged section, select SQL optimization and/or Access plan generation.

   Note: If no option is selected, the Activity Log is not created.

4. In the Information to be logged section, select SQL text or/and Access plan.

   The following information will be recorded automatically for each activity logged:

   Item	Description
   PC User	The user name from the PC
   Logon User	Database logon name
   Database Alias	Database alias name
   Schema	Schema name

5. Select the Directory Setup tab to change the directory where the Activity Log is stored. The default is the installation directory.

C:\Documents and Settings\username\Application Data\Quest Software\Quest SQL Optimizer for IBM® DB2® LUW.

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