Verifying the configuration of a database for tuning query workloads

Before you tune a query workload, ensure that the necessary tables and other objects exist on the DB2® database that the SQL statements in the query workload run on.

Before you begin

About this task

You perform this task in the Advanced Configuration and Privilege Management window in the IBM® Data Studio full client or administration client, version 3.1.1.

The key to the configuration of a database is creating the EXPLAIN tables. If one or more of those tables are missing, are at the wrong version and are not migratible, or have the wrong format, you must drop and recreate any existing EXPLAIN tables. You can then create them either by using the EXPLAIN.DDL file which is in the DB2 installation directory on the database or by using the Advanced Configuration and Privilege Management window in the IBM Data Studio full client or administration client. Your user ID must have DBADM authority.

If the IBM Data Studio full client or administration client determines that existing EXPLAIN tables on the database are migratible, the client displays a message with the DDL scripts for performing the migration.

After the EXPLAIN tables are created and are at the correct version and format, you can ensure that the tables for query workload tuning are created. The names of these tables start with QT_WCC. You create them through the Advanced Configuration and Privilege Management window, if they were not created when the license for InfoSphere® Optim™ Query Workload Tuner for DB2 for Linux, UNIX, and Windows was activated on the database.

Finally, you can enable the stored procedure that is required for the generation of index recommendations.

Procedure

To open the Advanced Configuration and Privilege Management window:

  1. In the Data Source Explorer, connect to the database, if you are not already connected to it.
  2. Right-click the database and select Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management.

What to do next

If there are any problems with the configuration of the database, messages appear both in pop-up windows and on the Advanced Configuration and Privilege Management window itself. Follow the instructions in these messages.

Feedback