You can learn more about query tuning using the InfoSphere Optim Performance Manager (OPM) web console. There are step-by-step
descriptions showing you how to tune and manage recommendations in the OPM web console. You can use
the OPM supplied tuning configuration scripts to configure the monitored database for tuning.
There are tips on troubleshooting tuning-related problems and database table space
management.

Cheung-Yuk Wu is a senior software developer in the IBM InfoSphere Optim Query Workload Tuner team. She has over 20 years of relational database tools development experience on DB2, Oracle, Sybase, Microsoft SQL Server, and Informix on Windows and UNIX platforms. She developed IBM software products, including Data Warehouse Edition SQW Admin Console and Design Studio, DB2
Content Manager and OnDemand, Tivoli for DB2, Data Hub for UNIX, and QMF. She was a DBA for DB2,
CICS, and IMS at the IBM San Jose Manufacturing Data Center.

Shannon Rouiller is an information architect and technical editor on the IBM InfoSphere
Optim/Data Studio team. Shannon has a BS in mathematics from Cal Poly, San Luis Obispo, and is a co-author of Developing Quality Technical Information and Designing Effective Wizards.

Vince Petrillo is the product manager for Database Performance Management Solutions. He has over 30 years experience in Software Development. At IBM, Vince has managed several development teams responsible for components included in products such as Data Studio, EGL and the Informix
Dynamic Server. He has also managed multiple QA teams within IBM. Prior to joining IBM, Vince worked as a director of development, QA manager, and an engineering manager over software and hardware development.

Cliff Leung is a distinguished engineer in Optim Data Studio at the Silicon Valley
Laboratory in San Jose, California. Cliff is the chief architect of Optim performance solution
and is responsible for the Optim Data Studio portfolio strategy and technical direction. Prior
to this role, Cliff was the architect for Optim Query Workload Tuner. Cliff has more than 20
years of experience in query compilation, optimization, and performance areas, both in DB2 for
Linux, UNIX, and Windows and DB2 for z/OS.

Tuning queries and
workloads from OPM

In Version 5.3 of InfoSphere OPM, you can tune queries or
workloads for monitored DB2® for Linux®, UNIX® and Windows®
and DB2 for z/OS® data servers, and view
the tuning recommendations within the same web console. You do not have to install Data Studio
for query or workload tuning.

This article describes how you can invoke query and workload tuning from the OPM web console and
how you can configure the monitored database for tuning if you need to. It also covers
best practices for query and
workload tuning, troubleshooting problems, and table spaces for storing tuning jobs in the OPM
web console.

Before you start tuning

If you plan on tuning workloads or using the priced single-query tuning features, you need
license entitlements for InfoSphere Optim Query Workload Tuner for the monitored database and
you must activate the database for query tuning.

After you activate the monitored database, you can configure the monitored database for tuning
by configuring the required EXPLAIN tables, Query Tuner tables, packages, and stored procedures
in the monitored database. OPM V5.3 provides SQL configuration scripts for DB2 for Linux, UNIX,
and Windows and JCL configuration files for DB2 for z/OS. You can find these files in the OPM
installation directory. For example, on Windows, the installation directory is C:\Program Files
(x86)\IBM\OPM\resources\QueryTunerConfig. Table 1 describes the files in the
QueryTunerConfig subdirectory:

If you have license for InfoSphere Optim Query Workload Tuner, you should configure all of the
features.

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V8NFM.

V9

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V9.

V10

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V10.

V10CM8

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V10CM8.

V10CM9

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V10CM9.

V11

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V11.

V11CM

Create query tuner tables, packages, and stored procedures for all tuning features against
DB2 subsystem V11CM.

WCC

Create query tuner stored procedure .jar file for workload tuning.

If the DB2 for Linux, UNIX, and Windows database does not have the required EXPLAIN tables under
your schema or SYSTOOLS, use the SQL script in the file
DB2_install_directory\MISC\EXPLAIN.DDL to create the necessary EXPLAIN tables.

Tip: If you want to configure your monitored database for tuning by using a
GUI, you can still use Data Studio to configure.

Starting tuning

After a monitored database is configured for tuning, you can use the menu options Tune with
This Web Console (for single-query tuning) and Tune All with This Web Console (for workload
tuning) from various dashboards in the OPM web console, as shown in Table 2:

Table 2. Tuning entry points in OPM

OPM web console page

Tune with This Web Console (Queries)

Tune All with This Web Console (Workloads)

SQL Statements dashboard

X

X

Extended In-flight dashboard

X

X

Locking dashboard

X

OPM reports

X

When you select Tune with This Web Console or Tune All with This Web
Console, you are prompted to select the tuning activities and options.

Tuning single queries

For example, suppose that you select an SQL statement on the SQL Statements dashboard for
monitored database sample976. As shown in the following set of screens, if you click
Tune with This Web Console, you see a dialog where you can choose the tuning activities to
use to get recommendations for the selected SQL statement:

Figure 1. Tuning single query from SQL dashboard

Click the Run action button in the dialog to generate the tuning
recommendations:

Figure 2. Select single query tuning activities to get
recommendations

When the recommendations are ready, you see the recommendation summary, recommended
actions, SQL statement, access plan summary, and relevant DB2 catalog information displayed in
the Query Tuning Results tab of the OPM web console, shown in Figure 3. You might want to save the recommendations
into an HTML report on the client for future reference. The single-query tuning recommendations
are replaced by the next single-query tuning results.

Figure 3. View tuning recommendations

Tuning workloads

You can also tune workloads from within the OPM SQL Statements dashboard after you have
collected the workload with the specified filters. Click on the Tune All with This
Console action menu.

Figure 4. Tune query workload from SQL dashboard

You are prompted to select the workload tuning activities and options. You can also
send email to multiple recipients upon job completion.

Viewing workload tuning jobs and results

Because workload tuning can be time consuming, a background tuning job is created for the
workload so that you can check the tuning status and results later even after you disconnect
from the OPM web console. For workloads, the tuning job and results persist in OPM until
the job retention time expires or until you delete the job.

When the workload tuning job completes, click the View Results button to
view the results, which include the workload recommendation, recommended actions, the workload
tuning options being selected to generate the recommendations, and the job log, shown
in Figure 6:

Figure 6. Manage workload tuning job results and status

You can refresh the job status and view the recently submitted jobs by clicking the
Refresh icon in the toolbar. Jobs submitted by other users appear after
refreshing the job list.

If you want to filter the job list, click the Define Filter icon in the
toolbar. You can filter the jobs by database name and other rules.

You can also cancel a running workload tuning job using the Cancel button if
you need to shut down the monitored database or other emergency cases.

You can customize the job retention by clicking the Job History Settings
button. The default job retention is 3 days. After you delete the job entry, you
cannot access the tuning results. If you change the job retention to Indefinitely, you
need to manually remove the job entries when you do not need them.

You can save the workload tuning recommendations to an HTML report on the client.

Figure 7. Open workload tuning results and save results

Implementing recommendations

You can run the recommended RUNSTATS commands and INDEXES statements using the job manager DB2
CLP Script. You can retune the query or workload to compare the performance differences.

Troubleshooting tuning

If the tuning job fails, you can review the job log in the bottom container for problem
diagnosis messages. You can turn on the query tuner log from the Open >
Services menu of the OPM web console. The query tuner logger name is oqwtJobMgrLogger,
which is automatically enabled after you do any tuning activities within your current OPM web
console session. The default log level is Information.

Best practices

The workload tuning job properties are stored in the OPM job manager tables; you need to
monitor the space utilization in these job manager tables under schema DSJOBMGR and table space
name CONTROL, as shown in Figure 10:

Figure 10. Manage table space usage for workload tuning jobs

The Job History Settings are shared among all of the jobs managed by the Job
Manager. You might
want to review the default job retention to suit all the jobs that you run in the OPM web
console.

Query Tuner advanced trace file is stored in the OS temporary directory. You might want to
monitor the space utilization and clean up these files when they are not needed.

Conclusion

This article explains how to perform single-query and workload tuning within the OPM web
console. You can find the query tuner configuration scripts for DB2 for Linux, UNIX and Windows,
and JCL files for DB2 for z/OS in the OPM install directory. You can manage the single-query and
workload tuning results and collect problem diagnosis messages without leaving the OPM web
console.

The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.