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 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

Ensure that you have the required authorities and privileges to configure a DB2 for Linux, UNIX, and Windows database and activate the license for InfoSphere® Optim™ Query Tuner.

Procedure

  1. Run the file EXPLAIN.DDL on the database that you want to use for tuning SQL. This file is located in the MISC folder in your DB2 installation.
  2. If you are using the IBM Data Studio full client, Version 3.1.1, the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version 3.1.1 License Activation Kit is installed on your workstation: Run the file License.bat (for Windows systems) or License.sh (for Linux systems), which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\License\, to activate the product 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.
    3. Run the file with the db2 -vtf command.
  3. In a text editor, edit the file luwsp.sql, which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\IA\, by replacing the $install variable with the absolute path to this file. 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.
  4. In a text editor, edit the file wccexplainsp.sql, which is in the directory <IBM Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\WCC\, by replacing the $install variable with the absolute path to this file. 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.
  5. Open an DB2 command window, connect to the database that you activated the license on, and change to the directory <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.
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.