Topics for configuring for IOQWT for DB2 LUW 3.1.1
You must configure your DB2® for Linux, UNIX, and Windows database
before you start tuning SQL statements.
The following objects are created on the database during
the configuration process:
- The license for InfoSphere Optim Query Workload Tuner
- 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 License Activation Kit, Version 3.1.1 is installed
on your workstation, you can activate the product license on the database.
You can activate the license either by running a script or by configuring
the database by using the IBM Data Studio full client.
- EXPLAIN tables
- You can create a set of EXPLAIN tables manually by running a script,
or you can let the IBM Data Studio client create a set in the SYSTOOLS
schema.
- QT_WCC* tables in the SYSTOOLS schema
- These tables store information about query workloads.
- The DB2OE.CALLDB2ADVIS stored procedure
- This stored procedure allows the Index Advisor and the Workload
Index Advisor to recommend new indexes.
- The OQT.WCC_EXPLAIN_SP stored procedure
- This stored procedure gathers EXPLAIN information for the SQL
statements that are in query workloads.
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 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 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.
If the client returns a message that EXPLAIN tables must
be dropped or migrated, follow the instructions in the message.
- 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 Analyze and Tune > Configure for Tuning > Advanced Configuration
and Privilege Management. 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 Analyze and Tune > Start Tuning.
Authorities and privileges for configuring DB2 for Linux, UNIX, and Windows databases
for use with IBM InfoSphere Optim Query Workload Tuner
Ensure that you have the authorities and privileges that
are required to configure a DB2 for Linux, UNIX, and Windows database.
You must have the following authorities and privileges to
configure a DB2 for Linux, UNIX, and Windows database
for query tuning:
- To activate the license for InfoSphere Optim Query Workload Tuner
on a database, you must have the authority or privilege to run the
CREATE FUNCTION statement.
- You must have the authority to install a JAR file
with a stored procedure on the database server. For more information,
see JAR file administration on the database server.
- You must have the authorities and privileges for running the CREATE
PROCEDURE (external) statement. For more information, see CREATE PROCEDURE (external) statement.
- You must have the authorities and privileges to create EXPLAIN
tables on your database, if these tables are not yet created.
Migrating EXPLAIN tables
If you upgraded the version of DB2 for Linux, UNIX, and
Windows that is managing a database, and that database contains a
set of EXPLAIN tables that you are using with IBM InfoSphere Optim
Query Workload Tuner, you can run a script to migrate those tables
so that they are compatible with the new version of DB2 for Linux,
UNIX, and Windows.
To migrate the EXPLAIN tables, follow the instructions
in the migrate_explaintables_db2luw.ddl migration
script.
You can find this migration script in these locations
in the installation directory for the IBM Data Studio client.
- On Windows: \QueryTunerServerConfig\all_features\LUW subdirectory
- On Linux or UNIX: /QueryTunerServerConfig/all_features/LUW subdirectory
Running scripts to configure DB2 databases for use with IBM
InfoSphere Optim Query Workload Tuner
If you do not want to use the IBM Data Studio client to
configure a DB2 for Linux, UNIX, and Windows database, you can carry
out the configuration by editing and running a number of scripts.
Before you begin
Ensure that you have the required authorities and
privileges to configure a DB2 for Linux, UNIX, and Windows database
and activate the license for InfoSphere Optim Query Tuner.
Procedure
- Run the file EXPLAIN.DDL on the database
that you want to use for tuning SQL. This file is located
in the MISC folder in your DB2 installation.
- If you are using the IBM Data Studio full client, Version
3.1.1, 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: Run the file License.bat (for
Windows systems) or License.sh (for Linux systems),
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\License\,
to activate the product license on the database.
- In a DB2 command window, connect to the database on
which you want to activate the license.
- Change to the directory in which the License.bat (for
Windows systems) or License.sh (for Linux systems)
file is located.
- Run the file with the db2 -vtf command.
- In a text editor, edit the file luwsp.sql,
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\IA\,
by replacing the $install variable with the absolute
path to this file. You do not need to run the file after
you edit it. In a later step, you will run a batch file that will
run this file.
- In a text editor, edit the file wccexplainsp.sql,
which is in the directory <IBM Data Studio installation
directory>\QueryTunerServerConfig\all_features\LUW\WCC\,
by replacing the $install variable with the absolute
path to this file. You do not need to run the file after
you edit it. In a later step, you will run a batch file that will
run this file.
- Open an DB2 command window, connect to the database that
you activated the license on, and change to the directory <IBM
Data Studio installation directory>\QueryTunerServerConfig\all_features\LUW\.
- Run the file enablement_win.bat (on
Windows systems) or enablement_LinuxUnix.sh (on
Linux systems).
- Check these output files in the current directory for errors: luwsp.out, oqt_profile.out, wccexplainsp.out,
and wccluwddl.out
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.
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.