Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
UsageStatistics Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

What does the SQL Scanner scan?

The SQL Scanner extracts SQL statements from the following items:

Item

Description

Database Objects

Database objects. The SQL Scanner only scans the database objects that contain SQL statements: materialized query tables, views, procedures, user-defined functions, triggers, packages, and plan tables.  

Event Monitors

Files or tables created by the DB2 Event Monitor. An Event Monitor collects data when certain events occur on the database such as the completion of the execution of a SQL statement.

Text/Binary Files

Application source code files and binary files.

COBOL Files

COBOL source code files.

 

Related Topic

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 image\B_Options.gif.

  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.

 

Related Documents