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.
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.
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.
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.
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.
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:
Special Registers Settings allows you to change the current degree, optimization class, and current path settings for the current session.
Select Database | Special Registers Settings.
Change the settings as required.
To reset back to the original database settings
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.
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.