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

Plan Settings

ClosedPlan tab options

The Plan settings are used to select the color and fonts used in the access plan.

Access Plan View Setting

Description

Color

Specify the color of the individual items in the access plan by clicking the Color column in the row for the item and selecting the new color from the dialog.

Font

Specify the font settings of the individual items in the access plan by clicking the Font column in the row for the item and selecting the new settings from the dialog.

To select which elements of the access plan are displayed

Right-click and select Plan Options.

 

Related Topic

SQL Scanner 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 Scanner Settings

The SQL Scanner settings are used to define the requirements for the SQL Scanner module.

SQL Scanner Options Settings

SQL Scanner General Settings

The settings in the SQL Classification tab are used by the SQL Scanner to help you identify which SQL statements are likely to be causing performance problems by classifying the SQL as Problematic, Complex, or Simple.

SQL Classification Settings

 

Related Topic

 

Options

ClosedView SQL Scanner tab – Options button options

The Options page on the SQL Scanner tab of the Options window allows you to specify criteria used when scanning for SQL statements.

Scanning Options

Description

Skip SQL within comments

Specify whether to ignore any SQL statements within comments enclosed by /* */, // and –- found in the source code. By default, the scanning algorithm will search for any SQL statements contained in comments.

Skip VALUES command that does not involve tables

Specify whether to ignore any VALUES command, such as: VALUES CURRENT SCHEMA.

Whole word matching for the first SQL keyword

Specify to search for SELECT, INSERT, UPDATE or DELETE as a whole word; the keyword must be preceded and followed by a space. The SQL Scanner therefore will not find the word INSERT in something like PROCEDUREINSERT and attempt to build a SQL statement from it.

Create Scanner Temp Table

Specify whether the scanning algorithm will automatically create temp table during scanning when a declare temp table statement is scanned. This option will not override any temp tables created in User-Defined Temp Tables window.

Include data

Specify whether Scanner Temp Table will include data. If this option is selected, the SQL Scanner will automatically execute any INSERT, DELETE, and UPDATE SQL statement that modifies Scanner Temp Table.

Note: Selecting the Include data option may affect the total scanning time.

Override previous Scanner Temp Table    

Specify whether to override a previous Scanner Temp Table if it scans another declare temp table statement using the same table name. This option will not override any temp tables created in User-Defined Temp Tables window.

Override User-Defined Temp Table

If selected, a previous User-Defined Temp Table is overwritten if the SQL Scanner finds another create temporary table statement using the same table name. This option will not override any temporary tables created in the SQL Scanner.

User temporary table space (for trigger conversion only)

Specify the user temporary table space used to create the temp table for the Trigger Conversion. If no user temporary table space is specified then the database default user temporary table space will be used.

Note: To create or modify temporary tables in the SQL Scanner the logon user needs the following privileges:

  • Connection to DB2 LUW 7 or above

  • USE privilege on the USER TEMPORARY table space or SYSADM or DBADM authority

Number of characters to be skipped at the beginning of every line for all files

When a file is scanned, the SQL Scanner skips the specified number of characters at the beginning of every line.

Skip end of line continuation character

If the text of the SQL statement is on more than one physical line and there is a continuation character at the end of each physical line of the SQL text, then specify what the continuation character is so that it will be skipped. If this character is not skipped, it may cause the SQL Scanner to miss part of the SQL statement. Three options are included: <Do not use>, / (forward slash character), and _ (underscore character). In addition, you may add your own character. This character will be saved in the box as long as it is the selected character. Once you make another selection, your own character will not be remembered. 

 

Related Topics

Related Documents