You can configure DB2 for Linux, UNIX, and Windows databases
for use the IBM InfoSphere Optim Query Workload Tuner by using the
IBM Data Studio client, Version 3.1.1.
Before you begin
- If an active license for IBM InfoSphere Optim Query Workload Tuner
for DB2 for Linux, UNIX, and Windows, Version 3.1.1 or Version 3.1
does not yet exist on the database that you want to configure, ensure
that you are using the IBM Data Studio full client, Version 3.1.1
and that the IBM InfoSphere Optim Query Workload Tuner for DB2 for
Linux, UNIX, and Windows License Activation Kit, Version 3.1.1 is
installed on your workstation.
If an active license for IBM InfoSphere
Optim Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version
3.1.1 or Version 3.1 exists on the database, you can use either IBM
Data Studio full client, Version 3.1.1 or IBM Data Studio administration
client, Version 3.1.1.
- Ensure that the client is connected to the
database..
- Ensure that you have the required authorities and
privileges to configure a DB2® for Linux, UNIX, and Windows database.
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 query tuning with InfoSphere Optim™ Query
Workload Tuner:
- 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 client detects whether a license
for IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 is active
on the database.
If there is an active license on the database,
the client attempts to configure the database for query tuning and
query-workload tuning.
If there is no active license on the
database, your choice of action depends on which of the three following
situations match your own situation:
- If you are using the IBM Data Studio administration client, Version
3.1.1, the client attempts to configure the database for query tuning
for the no-charge features only. For a list of the no-charge features,
see Features by product. If you do
not want to configure the database for the no-charge features only,
contact the person in your organization who installed both the IBM
Data Studio full client, Version 3.1.1 and the IBM InfoSphere Optim
Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version
3.1.1 License Activation Kit on a workstation and have that person
activate the license.
- If you are using the IBM Data Studio full client, Version 3.1.1
and the IBM InfoSphere Optim Query Workload Tuner for DB2 for Linux,
UNIX, and Windows, Version 3.1.1 License Activation Kit is not installed
on your workstation, the client attempts to configure the database
for query tuning with the no-charge features only. For a list of the
no-charge features, see Features by product. If you do
not want to configure the database for the no-charge features only,
contact the person in your organization who installed both the IBM
Data Studio full client, Version 3.1.1 and the IBM InfoSphere Optim
Query Workload Tuner for DB2 for Linux, UNIX, and Windows, Version
3.1.1 License Activation Kit on a workstation and have that person
activate the license.
- If you are using the IBM Data Studio full client, Version 3.1.1
and 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, the client attempts to configure the database
for both query tuning and query-workload tuning, if your user ID has
the authority to run the CREATE FUNCTION statement.
When the client attempts to configure the database, it
first checks whether at least one EXPLAIN table exists under the current
schema, which is the user ID. The client then follows this algorithm:
- If at least one EXPLAIN table exists under the current schema,
the client checks whether all EXPLAIN tables are present under the
current schema.
- If only a subset of EXPLAIN tables exists in the current schema,
the client reports that some of the EXPLAIN tables are missing. You
must drop the subset of EXPLAIN tables. Then, you can create a full
set of EXPLAIN tables in the SYSTOOLS schema by selecting the Guided
Configuration option again, or you can create a full set in a different
schema by running the EXPLAIN.DDL file that is in the MISC folder
of the installation directory for DB2 for Linux, UNIX, and Windows
on your database. After you create the tables, in the Data Source
Explorer connect to the database and select the Analyze and Tune >
Guided Configuration option again.
- If a full set of EXPLAIN tables exists in the current schema,
the client checks whether the EXPLAIN tables are at the version that
your database requires.
- If the EXPLAIN tables are at the version that your database requires,
the client carries out the remaining steps in the configuration process.
- If the EXPLAIN tables are not at the version that your database
requires, the client determines whether the tables can be migrated
to the correct version.
- If the EXPLAIN tables can be migrated, the client reports that
you must migrate the tables and provides the DDL statements for the
migration. After you run the DDL statements, in the Data Source Explorer
connect to the database and select the Analyze and Tune > Guided Configuration
option again.
- If the EXPLAIN tables cannot be migrated, the client reports that
you must drop the tables. Then, you can create a full set of EXPLAIN
tables in the SYSTOOLS schema by selecting the Guided Configuration
option again, or you can create them in a different schema by running
the EXPLAIN.DDL file that is in the MISC folder of the installation
directory for DB2 for Linux, UNIX, and Windows on your database. After
you create the tables, in the Data Source Explorer connect to the
database and select the Analyze and Tune > Guided Configuration option
again.
- If no EXPLAIN table exists under the current schema, the client
checks whether there is at least one EXPLAIN table in the SYSTOOLS
schema.
- If there are no EXPLAIN tables in the SYSTOOLS schema, the client
creates a full set of EXPLAIN tables in that schema and carries out
the rest of the configuration process.
- If there is at least on EXPLAIN table in the SYSTOOLS schema,
the client checks whether a full set of EXPLAIN tables exists in the
SYSTOOLS schema.
- If only a subset of EXPLAIN tables exists in the SYSTOOLS schema,
the client reports that some of the EXPLAIN tables are missing. You
must drop the subset of EXPLAIN tables. Then, you can create a full
set of EXPLAIN tables in the SYSTOOLS schema by selecting the Guided
Configuration option again, or you can create a full set in a different
schema by running the EXPLAIN.DDL file that is in the MISC folder
of the installation directory for DB2 for Linux, UNIX, and Windows
on your database. After you create the tables, in the Data Source
Explorer connect to the database and select the Analyze and Tune >
Guided Configuration option again.
- If a full set of EXPLAIN tables exists in the SYSTOOLS schema,
the client checks whether the EXPLAIN tables are at the version that
your database requires.
- If the EXPLAIN tables are at the version that your database requires,
the client carries out the remaining steps in the configuration process.
- If the EXPLAIN tables are not at the version that your database
requires, the client determines whether the tables can be migrated
to the correct version.
- If the EXPLAIN tables can be migrated, the client reports that
you must migrate the tables and provides the DDL statements for the
migration. After you run the DDL statements, in the Data Source Explorer
connect to the database and select the Analyze and Tune > Guided Configuration
option again.
- If the EXPLAIN tables cannot be migrated, the client reports that
you must drop the tables. Then, you can create a full set of EXPLAIN
tables in the SYSTOOLS schema by selecting the Guided Configuration
option again, or you can create them in a different schema by running
the EXPLAIN.DDL file that is in the MISC folder of the installation
directory for DB2 for Linux, UNIX, and Windows on your database. After
you create the tables, in the Data Source Explorer connect to the
database and select the Analyze and Tune > Guided Configuration option
again.
- 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.
What to do next
To start tuning in the
Query Tuner Workflow Assistant,
right-click the connection in the Data Source Explorer, then click .