SQL Optimizer for DB2 LUW 4.4 - User Guide

Add SQL Inspectors

You may want to scan the SQL Inspector to identify the SELECT, INSERT, UPDATE and DELETE SQL statements with their query plan, abstract plan and trace on information (if available). This enables you to review the query plan and helps to give a better understanding of which SQL statement is causing the performance problem within the database server.

To add a SQL Inspector

1. Click the SQL Inspector page. The left-hand list field displays a list of available Inspectors. If you have not yet created any Inspectors, this list field is blank.

2. Select an Inspector and click or drag the Inspector you want to scan over to the right-hand list field.

3. Use the Set Scheme list to select the schema that corresponds with the SQL that you are scanning.

Related Topic

Add Jobs to the Job Manager Window

Summary

The Summary page in the Add Jobs wizard in the SQL Scanner shows a list of the jobs that were selected for scanning.

Scan

The SQL Scanner only scans Jobs that are "marked." The Jobs are automatically marked when they are added by the Add Jobs wizard.

To mark a Job

Clicking the Job row. The red checkmark will be prefixed to the File / Database Object column. Multiple Jobs can be marked at a time. If there are no marked Jobs, the Scan button is dim and scanning cannot begin.

To unmark a Job

Click the marked row.

To scan the marked Jobs

Click .

During and after scanning, information is updated on the Job Manager window and the sign is replaced with sign. The time required to scan a Job depends on the system's processor speed, the file size and the number of valid and invalid SQL statements. See Scanned SQL Statement Classification for more information about invalid statements.

To abort the scanning process

Click .

Scan Source Code with Temporary Tables

During scanning, if the scanned SQL statement is used to create or modify a temporary table, the SQL will be automatically executed if the Create Scanner Temp Table checkbox in the Options is selected. The created Scanner Temp Tables will be dropped after the job has finished scanning.

Example

Source Code

declare global temporary table session.ABC

  like EMPLOYEE

  on commit delete rows

  not logged

 

select *

  from session.ABC

After scanning in the Scanned SQL Viewer, you will see the SQL statement displayed in the Scanned SQL pane as:

select *

  from session.ABC   

The DDL for creating the temporary tables is displayed under the Scanner Temp Table tab in the SQL Information. This includes the DDL found by the SQL Scanner or the DDL used to create the User-Defined Temp Table.

declare global temporary table session.ABC

  like EMPLOYEE

  on commit delete rows

  not logged

Note: To create or modify temporary tables SQL Scanner, the logon user needs the following privileges:

• Connection to DB2 LUW 7 or above

• USE privilege on the USER TEMPORARY table space or SYSADM or DBADM authority.