Configuring DB2 databases for query and query-workload tuning by using the IBM Data Studio full client, Version 3.1.1

You can configure DB2® for Linux, UNIX, and Windows databases for use with IBM® InfoSphere® Optim™ Query Workload Tuner by using the IBM Data Studio full client, Version 3.1.1.

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.

About this task

During configuration, when InfoSphere Optim Query Workload Tuner creates the EXPLAIN tables and the EXPLAIN_GET_MSGS user-defined function, it grants the EXECUTE privilege on the EXPLAIN_GET_MSGS user-defined function to PUBLIC.

Procedure

To configure a DB2 for Linux, UNIX, and Windows database for use with InfoSphere Optim Query Workload Tuner:

  1. In the Data Source Explorer, right-click the connection to the database and select Connect.
  2. Expand the connection by clicking the plus symbol next to it. An icon that represents the database appears.
  3. Right-click the database and select Analyze and Tune > Configure for Tuning > Guided Configuration.

    The client detects whether a license for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 is active on the database.

    If a license is active on the database, the client attempts to configure the database for query tuning and query-workload tuning.

  4. If the client returns a message that EXPLAIN tables must be dropped or migrated, follow the instructions in the message. Then, repeat step 2.
  5. Verify that the database is configured for query and query-workload tuning and that the required data objects are created or enabled.
    1. In the Data Source Explorer, right-click the database, and then click Analyze and Tune > Configure for Tuning > Advanced Configuration and Privilege Management. The Advanced Configuration and Privilege Management window opens.
    2. In the Configuration Status section, you can check the status of the tables, stored procedures, objects, and automatic statistics collection that are required to use the query and query-workload tuning features. If a data object is disabled, follow the instructions in the message that is displayed beside that object to create or enable it.

Results

Your DB2 for Linux, UNIX, and Windows database is configured for query and query-workload tuning with InfoSphere Optim Query Workload Tuner.

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.

Feedback