Running scripts to configure DB2 databases for use with IBM InfoSphere Optim Query Workload Tuner

If you do not want to use the IBM® Data Studio client, Version 4.1.1 to configure a DB2® for Linux, UNIX, and Windows database, you can accomplish the configuration by editing and running a number of scripts.

Before you begin

Recommendation: In the DB2 for Linux, UNIX, and Windows database that you are configuring, create a system temporary table space with a page size of 8 KB or larger to avoid potential query tuning errors.

Procedure

To run scripts to configure a DB2 for Linux, UNIX, and Windows database:

  1. Run the file EXPLAIN.DDL on the database that you want to use for tuning SQL. This file is in the MISC folder in your DB2 installation.
  2. Activate the full set of tuning features on the database.
    1. In a DB2 command window, connect to the database on which you want to activate the full set of tuning features.
    2. Change to the directory in which the License.bat (for Windows systems) or License.sh (for Linux systems) file is located.
      • On Windows: IBM_Data_Studio_installation_directory\QueryTunerServerConfig\all_features\LUW\License\
      • On Linux: IBM_Data_Studio_installation_directory/QueryTunerServerConfig/all_features/LUW/License/
    3. Run the file that is appropriate for your operating system.
  3. In a text editor, edit the file luwsp.sql by replacing the $install variable with the absolute path of the installation directory for the IBM Data Studio client, Version 4.1.1. For example, on 32-bit Windows systems, the default absolute path is C:\Program Files\IBM\DS4.1.0. You do not need to run the file after you edit it. In a later step, you will run a batch file that will run this file.
    Both the luwsp.sql and luwsp.jar files are located in this directory:
    • On Windows: IBM_Data_Studio_installation_directory\QueryTunerServerConfig\all_features\LUW\IA\
    • On Linux: IBM_Data_Studio_installation_directory/QueryTunerServerConfig/all_features/LUW/IA/
  4. In a text editor, edit the file wccexplainsp.sql by replacing the $install variable with the absolute path of the installation directory for the IBM Data Studio client, Version 4.1.1. For example, on 32-bit Windows systems, the default absolute path is C:\Program Files\IBM\DS4.1.0. You do not need to run the file after you edit it. In a later step, you will run a batch file that will run this file.
    Both the wccexplainsp.sql and wccexplainsp.jar files are located in this directory:
    • On Windows: IBM_Data_Studio_installation_directory\QueryTunerServerConfig\all_features\LUW\WCC\
    • On Linux: IBM_Data_Studio_installation_directory/QueryTunerServerConfig/all_features/LUW/WCC/
  5. Open a DB2 command window, connect to the database that you activated the full set of tuning features on, and change to the following directory:
    • On Windows: IBM_Data_Studio_installation_directory\QueryTunerServerConfig\all_features\LUW\
    • On Linux: IBM_Data_Studio_installation_directory/QueryTunerServerConfig/all_features/LUW/
  6. Run the file enablement_win.bat (on Windows systems) or enablement_LinuxUnix.sh (on Linux systems).
  7. Check these output files in the current directory for errors: luwsp.out, oqt_profile.out, wccexplainsp.out, and wccluwddl.out

What to do next

If you need to grant privileges to other people that will tune SQL statements on the database, in the Data Source Explorer right-click the database and select Analyze and Tune > Manage Privileges for Tuning. In the Manage Privileges on Tables for Workload Tuning window, add the user ID that you want to grant privileges to.

Feedback