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 client, Version 4.1.1.
Before you begin
Recommendation: In the DB2 for Linux, UNIX, and Windows database
that you are configuring, create a 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 for DB2 for Linux, UNIX, and Windows:
- In the Data Source Explorer, right-click the connection
to the database and select Connect.
- Expand the connection by clicking the plus symbol next
to it. An icon that represents the database appears.
- Right-click the database and select .
The IBM Data Studio client detects whether
the full set of tuning features for IBM InfoSphere Optim Query Workload Tuner, Version 4.1.1 is
activated on the database.
If the full set of tuning features
is activated on the database, the IBM Data
Studio client attempts to configure the database for query tuning
and query-workload tuning.
If the full set of tuning features
is not activated on the database, you can activate it if the following
two conditions are true:
- The InfoSphere Optim Query Workload Tuner License
Activation Kit is installed with the IBM Data
Studio client.
- Your user ID has the authority or privilege to run the CREATE
FUNCTION statement.
To activate the full set of tuning features by using
the Data Studio client:
- In the Data Source Explorer, expand the connection to
the database.
- Right-click the database and select . After
the full set of tuning features is activated, right-click the database
and select .
To activate the full set of tuning features by running
a script:
- Run the License.bat file (on Windows) or the License.sh file
(on Linux) that is created
in the installation directory of the Data Studio client. By
default, these files are located in the directory C:\Program
Files\IBM\DS4.1.0\QueryTunerServerConfig\all_features\LUW\License.
- Return to the Data Studio client to continue the configuration
of the database. Right-click the database and select .
- If the client returns a message that EXPLAIN tables must
be dropped or migrated, follow the instructions in the message. Then,
repeat step 2.
- Verify that the database is configured for query and query-workload
tuning and that the required data objects are created or enabled.
- In the Data Source Explorer, right-click the database,
and then click . The Advanced
Configuration and Privilege Management window opens.
- 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 for DB2 for Linux, UNIX, and Windows.
What to do next
If you need to grant privileges to other people that will
tune SQL statements on the database, see Authorities
and privileges that are required for capturing and tuning SQL statements
and query workloads that run on DB2 for Linux, UNIX, and Windows.
To
start tuning in the Query Tuner Workflow Assistant, right-click the
connection in the Data Source Explorer, then click .