SQL Optimizer for DB2 LUW 4.4 - Installation Guide

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.