Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.3.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
About SQL Optimizer for IBM DB2 LUW 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


ClosedView SQL Rewrite tab – Quota options  

The Quota page under the SQL Rewrite tab on the Options window allows you to restrict the number of SQL transformations produced during SQL Rewrite process. You can modify quota information only when Intelligence level is set to Custom (on the Intelligence page).



Syntax Transformation Quota

(Default = 80, Range = 1 to 99,999)

Specify the maximum number of SQL statements generated by applying SQL transformation rules. This quota affects how many SQL statements are created by changing the SQL syntax. The default quota of 100 is normally sufficient for most of the complicated SQL statements. However, the quota can be increased to tackle exceptionally complicated SQL statements with very high levels of table joins or multiple levels of nested sub-queries.

SQL Options Quota Ratio (%)

(Default = 10%, Range = 1% to 100%)

This percentage is used to calculate the maximum number of SQL statements generated by applying SQL options.

Number of SQL options selected

This is a read-only field indicating the number of SQL options selected. This figure is used to calculate the maximum number of SQL statements generated by applying SQL options.

SQL Options Quota =

(Syntax Transformation Quota * SQL Options Quota Ratio%) * Number of SQL Options selected

Total Quota

This is a read-only field indicating the maximum number of SQL statements generated during SQL Rewrite. This figure consists of: Syntax Transformation Quota + SQL Options Quota.

Table Join Permutation Quota

(Default = 60, Range = 0 to 999,999)

Specify the maximum number of table join access path that will be considered duringSQL Rewrite.

Caution: The higher the quota, the longer it might take to rewrite a complicated SQL statement.


Related Topics


ClosedView SQL Rewrite tab – General options  

The General page under the SQL Rewritetab in the Options window provides the option to start a Batch Run immediately after the SQL Rewrite process is finished.

Batch Run

Batch Run


Automatically start Batch Run after rewriting

Specify to start the Batch Run function on all the SQL alternatives as soon as the SQL rewriting is finished.


Related Topics

Editor Settings

ClosedView Editor tab options

The Editor tab on the Options window allows users to define the editable panes’ controls and layout.


General Settings


Use tab characters

Specify whether to use the tab character (ASCII 9) instead of spaces.

Smart tab (Default)

Specify whether to start a new line at the first non-whitespace character of the preceding line.

Bracket pairing (Default)

Specify whether to match the corresponding opening bracket when the end bracket is typed. This applies to the ( [{ characters.

Use lowercase for object name (Default)

Specify whether to use lowercase for database object names.

Block cursor for overwrite (Default)

Specify whether to change to block cursor for overwrite mode. Overwrite mode is when text entered at the cursor will overwrite existing text.

Show all characters

Specify whether to show all characters including spaces, new lines and tabs.

Show gutter (Default)

Specify whether to show a non-editable boarder on the left of the Editor pane.

Width (Default = 34, Range = 1 to 100 pixel)

Specify the gutter width.

Show line numbers in gutter (Default)

Specify whether to show line numbers in the gutter.

Show right margin (Default)

Specify whether to show a vertical line at the right margin.

Width (Default = 80, Range = 1 to 1000 characters)

Specify the width of the page or the right margin position.

Block indent step size

Specify the block indent step size in character used by the Indent/Outdent function.

Tab size

Specify the character length of a tab.


Editing Settings


Syntax highlight (Default)

Specify whether to use syntax highlight. If selected, it is possible to define the format for the following syntax: reserved word, comment, identifier, quoted identifier, string, number, symbol, data type, default exception, hint, text selection, member lookup and argument lookup.

Auto Correction (Default)

Specify whether to automatically correct typing errors as characters are typed. Allows the addition, editing and deletion of the auto correction entry.


Lookup Settings


Member lookup (Default)

Specify whether to show the lookup hint for database object members. For example, displays a list column names when a table name is specified.

Argument lookup (Default)

Specify whether to show the argument parameters hint for database functions and procedures.

Delay (Default = 0.75 sec, Range = 0.5 to 1.5 sec )

Specify the delay time before the lookup hint appears.


Related Topics

Activity Log Settings

ClosedView Activity Log tab options

The Activity Log tab on the Options window allows users to specify whether to record the access plan generation and optimization activities during daily operations.

Activities to be logged



SQL optimization

Specify to log the SQL optimization activities.

Access plan generation

Specify to log each time an access plan is retrieved.

The Activity log will record the activities from SQL optimization process and retrieval of an access plan. If SQL optimization or Access plan generation checkboxes are not selected, then no activities are recorded in the activity log. By default, no activities are logged.  

Information to be logged



SQL text

Specify to save the text of the SQL statement.

Access plan

Specify to save the access plan for the SQL statement.

In addition to SQL text and access plan, the login user, OS user, schema, elapsed time, and SQL type are recorded automatically.




Show warning message when log file exceeds

(Default = 5MB, Range from 1 to 500MB)

Specify the maximum size in MB of the activity log file. If the size of the activity log file exceeds the maximum value, a warning message is displayed.

Purge activity log

Use to remove information from the activity log.

To remove information

  1. Select either Whole log to remove all information


    Specify a date range to remove logs between these dates.

  2. Click Purge Now to remove the desired logs.


Related Topic

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating