SQL Optimizer for DB2 LUW 4.4.1 - User Guide

What does the SQL Scanner scan?

The SQL Scanner extracts SQL statements from the following items:

Related Topic

Embedded and Dynamic SQL Statements

Scanned SQL Statement Classification

All SQL statements retrieved by SQL Scanner are syntactically correct. After the SQL statements have been identified, the access plan is retrieved. If the access plan is not retrieved successfully, then these SQL statements are not valid. All valid SQL statements are further classified as Simple, Complex and Problematic SQL statements. The definitions of these classified SQL statement types are dependent on user-defined parameters in the Options window.

Problematic SQL Statements

Problematic SQL statements are potentially problematic SQL statements that should be optimized. Problematic SQL should satisfy one of the following criteria:

• The number of tables referenced in the access plan of a SQL statement exceeds the upper limit of the Complex table scan operation range (option to include or exclude OLD_TABLE or NEW_TABLE simulated temp table for Triggers).

• At least one table full scan with table size larger than the threshold size. The default threshold size is 1 page which it can be changed in Options.

• At least one table full scan executed in a nested loop with table size larger than the threshold size. The default threshold size is 1 page which it can be changed in Options.

Complex SQL Statement

Complex SQL statements are complicated SQL statements where there is room for improvement. Complex SQL statements are SQL statements that involve a certain number of table references in their access plans. If the number of tables referenced in the access plan of an SQL statement falls into the Complex table scan operation range (option to include or exclude OLD_TABLE or NEW_TABLE simulated temp table for Triggers) defined in the Options window, this SQL statement will be classified as a Complex SQL statement

Simple SQL Statement

Simple SQL statements are direct and straightforward SQL statements with minimal probability of improvement. SQL statements are defined as Simple SQL statements when number of tables referenced in the access plan is l than the lower limit of Complex table scan operation range defined in the Options window, the default value being less than 2 tables.

No Plan SQL Statements classified as "Invalid SQL"

If the execution plan is not retrieved successfully, then the SQL statement is classified as Invalid SQL. A SQL statement is invalid if the database object it references does not exist, the database user does not have privileges to access the database object, or the schema used is not the correct one for the SQL statement.

Data Directory Setting

SQL Scanner data directory is the location to store the data files created while executing the Scan function. The data directory path is stored in the Options window.

To change the directory path

1. Click .

2. Select the Directory Setup tab.

3. Click the SQL Scanner data directory box.

4. Click Browse.

5. Select a directory for storing the Scanner files.

Changes to this directory cannot be made while SQL Scanner is active.

Note: It is advisable not to change the data directory after selection, as files created during scanning will be kept in this directory.

SQL Scanner Conversions

In order to render the SQL statement as a valid standalone SQL from other program syntax, one or more conversions may need to be applied to the SQL statement.

Conversions includes:

Trigger Conversion

Parameter Markers Conversion

Local Variable Conversion

Quoted String Conversion

COBOL Conversion

If the above conversion had been applied to the SQL statement, the Information pane shows what conversions had been applied and what changes had been made to the SQL text.

Note: If conversion had been applied, it may be necessary to reverse the changes after optimization when it is pasted back to the original source code.