Configuring a DB2 for Linux, UNIX, and Windows database for query tuning

You must configure your DB2® for Linux®, UNIX®, and Windows® database for the Optim™ Query Tuner client before you start tuning SQL statements with it.

Before you begin

About this task

You can configure your DB2 for Linux, UNIX, and Windows database from the client to create EXPLAIN and ADVISE tables and enable the Index Advisor stored procedure. If these objects exist in the database in a different format or version, you see a message. Follow the instructions in the message to migrate these objects to the current version.

If you are configuring your database directly on the server without using the client, follow the instructions in the migrate_explaintables_db2luw.ddl file to migrate the EXPLAIN and ADVISE tables. The SQL statements that are used for migrating the EXPLAIN and ADVISE tables are provided in this file. You can find this file in the following locations.
  • On Windows server: \serverconfig\LUW subdirectory of the installation directory for the server-activation program. The default installation directory is C:\Program Files\IBM\OQTS.
  • On Linux or UNIX server: /serverconfig/LUW subdirectory of the installation directory for the server-activation program. The default installation directory is /opt/IBM/OQTS.
  • On the client system: \serverconfig\LUW\ subdirectory of the installation directory for the client. The default installation directory is C:\Program Files\IBM\OQT2.2.1.

Procedure

To configure a DB2 for Linux, UNIX, and Windows database for query tuning:

  1. In the Data Source Explorer, right-click the database to configure and click Query Tuner > Configure for Tuning > Guided Configuration.

    A message indicates whether the configuration is successful.

  2. If you see a message that the format of some tables is not correct during the configuration process, follow the instructions in the message to migrate the EXPLAIN and ADVISE tables by running the given SQL statements on the database.
  3. Verify that the database is configured for query tuning and that the Index Advisor stored procedure is enabled:
    1. In the Data Source Explorer, right-click the database, and then click Query Tuner > Configure for Tuning > Advanced Configuration and Privilege Management. The Advanced Configuration and Privilege Management window opens.

      You can check the status of the EXPLAIN tables, the Index Advisor stored procedures, automatic statistics collection, and the query tuning features.

    2. If the EXPLAIN tables are not enabled, follow the instructions in the message to enable the EXPLAIN tables.
    3. If the Index Advisor stored procedure is not enabled, follow the instructions in the message to migrate or enable the stored procedure.
  4. Close the Advanced Configuration and Privilege Management window.

Feedback