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

Connect to the Database

Prior to installing SQL Optimizer, a client/server system needs to be set up. Set up the host strings in DB2 Client Configuration Assistant to connect to the databases.

SQL Optimizer directly uses the DB2 Client Configuration Assistant to connect to the DB2 LUW database.

Loading information from the Data Dictionary

Information about the tables and indexes from the data dictionary needs to be loaded into the memory of the computer for every connection to a database. The Load data dictionary after database connection option determines whether information is loaded when the connection is made or whether the specific information needed from the database is loaded when a SQL statement is parsed for functions such as scanning, optimization, and index generation.

 

Related Topic

User Logon Privileges

Every database has some kind of database privileges. These privileges are directly reflected in SQL Optimizer to limit the access and authority of each user. When logging on to SQL Optimizer, if you do not satisfy the logon privileges below, you will be presented with a Connection Information window. The Connection Information window details reason and the workaround.

Temporary Table Generation

To create or modify temporary tables in User-Defined Temp Table, Scanner Temp Table, Trigger Conversion and temporary table generation while optimizing the original SQL statement, 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.

Index Expert Function

The Index Expert function requires DB2 LUW version 7 or later to retrieve the indexes recommended by DB2 LUW functions. It requires DB2 LUW version 8 or later to use the Generate Indexes function with the Index Expert’s unique Artificial Intelligence engine. It also requires that the statistics be run in order to be able to estimate the size of the index.

SQL Optimizer Module

On the Access Plan tab in the SQL Optimizer window, you can update the table and index statistics. In order to update statistics, you must have one of these privileges:

SYSADM

SYSCTRL

SYSMAINT

DBADM

CONTROL privilege on the table

LOAD authority

 

Special Registers Settings

Special Registers Settings allows you to change the current degree, optimization class, and current path settings for the current session.

To change the special register settings

  1. Select Database | Special Registers Settings.

  2. Change the settings as required.

  3. Click Apply.

To reset back to the original database settings

Click Reset.

If the special registers settings had been changed you will notice icon appearing on the bottom right of the main window status. This icon will disappear if the special registers settings correspond to the database settings.

 

Related Topic

Synchronize the Data Dictionary

Specific database information, such as tables, indexes, data volumes, and so on, from the data dictionary is used during the optimization process, SQL analysis, and other functions throughout the program. This information can be loaded into memory of your local computer each time you connect to a database. If your database has lots of database object, this process can take several moments, therefore you can choose to have the specific information loaded as it is needed in the program by selecting the Do not load database dictionary after database connection in the General page of the Options window.  

If changes are made to the database while you are using the program, it is important to keep the information in the data dictionary up to date. Using the Synchronize Data Dictionary function will ensure that the changes to the database are directly reflected in SQL Optimizer.  

To synchronize the database dictionary

Select Database | Synchronize Data Dictionary.

The Synchronize Data Dictionary function does not break the connection but updates the new database information in the SQL Optimizer memory.

 

Related Documents