SQL Optimizer for DB2 LUW 4.4.1 - Installation Guide

Configure SQL Optimizer

Database requirements

SQL Optimizer supports DB2 LUW 9/10 on UNIX (AIX, Solaris, HP-UX, Linux), OS/2, and Windows.

Privileges

Most of the functionality in SQL Optimizer does not need any special privilege for the user. When logging on to SQL Optimizer, if you do not satisfy the logon privileges below, you are presented with a Connection Information window that details the functionality in SQL Optimizer which needs the privilege and therefore may be limited if the user logon is not granted the privilege.

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 source SQL statement, the logon user needs the following privileges:

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

SQL Optimizer Module

In the Access Plan page of 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

Troubleshoot SQL Optimizer

"Failed to Connect to IBM DB2 LUW"

Check that other DB2 products (for example, Command Center) connect successfully to the database with the same login name, password and database alias. If connection fails, check with your database administrator.

No Index Recommendations from IBM DB2 LUW

If the Generate Indexes function does not provide indexes recommended by DB2 LUW, perhaps the MEM_UDF heap size is not set large enough. Refer to the IBM documentation to increase the size of UDF Shared Memory in DB2 LUW to 1024 or higher.