General
The General page on the SQL Scanner tab of the Options window allows you to specify your options for the graphical interface and the Checked SQL feature in the SQL Scanner.
Interface
|
Interface Settings |
Description |
|---|---|
|
When checked, the SQL statements are displayed with color-coded tabs representing the SQL’s classification. Red for Problematic, purple for Complex, green for Simple, and blue for Invalid. If the SQL statement is checked, the tab is gray. | |
|
When checked, an additional column of numbers is added to the SQL classification columns to indicate how many SQL statements have been checked for each Job. When a SQL statement is added to the Checked SQL list, it has a value in the appropriate SQL classification column in the Job Manager window. |
Checked List
|
Checked List Settings |
Description |
|---|---|
|
Action when performing Send to SQL Optimizer or Send to Index Expert | |
|
If selected, you are prompted to be added to the Checked List. | |
|
If selected, all SQL sent to the SQL Optimizer is added to the Checked List. | |
|
If selected, all SQL sent to the SQL Optimizer is not automatically added to the Checked List. | |
|
Do not remove Check SQL information when rescanning |
Specify to keep the checked SQL information when the Job is rescanned. If unchecked, the checked SQL information is removed when the Job is scanned again. |
|
Compare the current SQL with the rescanned SQL using | |
|
SQL text |
Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using only the SQL text as a comparison for the match. The checked SQL information is preserved for those SQL statements where the SQL text matches. |
|
SQL text and access plan |
Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using the SQL text and the access plan as a comparison for the match. The checked SQL information is only preserved for those SQL statements where the access plan and the SQL text match. |
SQL Classification Settings
Quest SQL Optimizer for IBM® DB2® LUW maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)
SQL Classification Settings
The SQL Classification tab on the Options window allows users to specify the criteria to analyze a SQL statement. If the SQL statement analyzed satisfies any of the settings below, it will either be classified as Complex or Problematic SQL. Problematic SQL statement indicates potential problematic SQL statement and should be optimized, while complex SQL statements are complicated SQL statements where there is room for improvement.
The following settings are used to set the criteria for Simple, Complex and Problematic SQL statements.
Simple SQL
Complex SQL
|
Complex SQL Criteria |
Description |
|---|---|
|
Number of table scan operations (Default = 2 /3, Range 2 to 99) |
Specify the range of the number of table scan operations referenced in the access plan for Complex SQL statements. |
|
Including OLD_TABLE simulation temp table |
Specify to count the OLD_TABLE simulation temp table created in Trigger Conversion. |
|
Including NEW_TABLE simulation temp table |
Specify to count the NEW_TABLE simulation temp table created in Trigger Conversion. |
Problematic SQL
|
Problematic SQL Criteria |
Description |
|---|---|
|
Number of table scan operations greater than (Default = 3) |
Read-only field indicating the number of table scan operations references in the access plan. If the total number of table scan operation is greater than this value, then this SQL statement will be classified as Problematic. This value is the same as the upper limit of the complex table scan operations range. |
|
Specify whether SQL statements with a single table full scan with table size greater than or equal to the defined page size (in Ketose) will be classified as Problematic SQL statements. | |
|
With full table scan iterated by nested loop |
Specify whether SQL statements with a full table scan inside a nested loop are classified as Problematic SQL statements. This classification depends upon the number of pages in a table. |
|
Number of pages (available only if statistics are gathered) (Default = 1, Range= 1 to 9999996) |
Specify the number of pages in the table. |
|
Including OLD_TABLE simulation temp table |
Specify to count the OLD_TABLE simulation temp table created in Trigger Conversion. |
|
Including NEW_TABLE simulation temp table |
Specify to count the NEW_TABLE simulation temp table created in Trigger Conversion. |
SQL Statement with no Access Plan
In addition to being classified as Simple, Complex, or Problematic, some SQL statements are classified as "Invalid SQL" to indicated that access plan could not be retrieved from the database. The DB2 LUW error message which indicates that the access plan was not retrieved is displayed to help you determine why the SQL statement was classified as invalid. The Invalid SQL classification may result from one of the following:
|
Reason for Invalid SQL Classification |
Explanation |
|---|---|
|
No permission to tables or views |
The current user does not have privilege to the tables, views, or other database objects referenced in the SQL statement even though the syntax of the SQL statement is correct. |
|
The SQL Scanner is unable to identify SQL statements that are dynamically constructed at run time. This type of SQL is found in the source code on several command lines and the exact construct of the SQL statement is not determine until the application is executed. The DB2 Event Monitor can be used to trap all executed or real-time SQL statements. | |
|
Database object does not exist |
A database object references in the SQL statement does not exist. |
|
Incorrect Schema |
The schema that was used when the execution plan was retrieved was not the correct schema for the SQL statement. |