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 full client, Version 3.1.1 to configure a DB2® for Linux, UNIX, and Windows database, you can carry out 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 license on the database.
    1. In a DB2 command window, connect to the database on which you want to activate the license.
    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 with the db2 -vtf command.
  3. In a text editor, edit the file luwsp.sql by replacing the $install variable with the absolute path to the file luwsp.jar. 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 to the file wccexplainsp.jar. 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 license 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

To start tuning in the Query Tuner Workflow Assistant, right-click the connection in the Data Source Explorer, then click Analyze and Tune > Start Tuning.