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

General

ClosedView SQL Scanner tab – General button options

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

Use color tabs for SQL classification

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.

Show checked SQL figures on the Job Manager window

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

Prompt user for action

If selected, you are prompted to be added to the Checked List.

Always add SQL

If selected, all SQL sent to the SQL Optimizer is added to the Checked List.

Never add SQL (default)

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.

 

Related Topics

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 Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer

 

SQL Classification Settings

ClosedView SQL Classification tab options

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

Simple SQL Criteria

Description

Number of table scan operations less than

(Default = 2)

Read-only field indicating the number of table scan operations references in the access plan. If the total number of table scan operations is less than this value, then this SQL statement will be classified as Simple. This value is the same as the lower limit of the complex table scan operations range.

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.

With full table scan

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.  

 

Related Topic

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.

Dynamic SQL statements

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.

 

Related Topic

Related Documents