Tuning queries and workloads from InfoSphere Optim Performance Manager

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.

Share:

Cheung-Yuk Wu (wu@us.ibm.com), Senior Software Developer, IBM

  Cheung-Yuk WuCheung-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 (shannonr@us.ibm.com), Information Architect and Technical Editor, IBM

Shannon RouillerShannon 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.



Robert Heath (heathr@us.ibm.com), Information Developer, IBM

  Robert HeathRobert Heath has been with IBM for 13 years and is the information developer for InfoSphere Optim Query Workload Tuner.



Vincent Petrillo (vpetrill@us.ibm.com), Product Manager, IBM

Vincent PetrilloVince 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 (cleung@us.ibm.com), Distinguished Engineer, IBM  

Cliff LeungCliff 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.



31 October 2013

Also available in Chinese Russian

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.

Table 1. Tuning configuration scripts and JCL files for DB2 LUW and DB2 z/OS
Database platformSubdirectories in all_featuresDescription
LUWIACreate a query tuner stored procedure to generate index recommendation for single-query.
LicenseActivate a monitored database.
VPHNot used by tuning in OPM.
WCCCreate query tuner stored procedure and meta tables to tune workloads.
ZOSmigrationMigrate query tuner metadata from V3.2 to V4.1 for DB2 Subsystem V8 to V11NFM.
PerformanceNot used by tuning in OPM.
SCANot used by tuning in OPM.
V8NFMCreate query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V8NFM.
V9Create query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V9.
V10Create query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V10.
V10CM8Create query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V10CM8.
V10CM9Create query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V10CM9.
V11Create query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V11.
V11CMCreate query tuner tables, packages, and stored procedures for all tuning features against DB2 subsystem V11CM.
WCCCreate 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 pageTune with This Web Console (Queries)Tune All with This Web Console (Workloads)
SQL Statements dashboardXX
Extended In-flight dashboardXX
Locking dashboardX
OPM reportsX

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
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
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
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
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.

Figure 5. Select workload tuning activities for recommendations and notification
Select workload tuning activities for recommendations and notification

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
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
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.

To turn on advanced query tuner trace, change the log level to Trace, shown in Figure 8. The advanced query tuner trace file path is listed in the Query Tuner logger oqwtJobMgrLogger.

Figure 8. Enable advanced tuning API trace for pro
Enable advanced tuning API trace for pro

To open the logger oqwtJobMgrLogger, click Open > Logs in the OPM web console, shown in Figure 9:

Figure 9. View query tuning logger content
View query tuning logger content

You will find the following log record where the query tuner advanced trace file path is. The query tuner trace file name starts with qtTrace, for example, qtTrace-GLACIER$.20130618T163329Z-ejtm.trc.

Click to see code listing

oqwtJobMgrLogger	(55)	Jun 18, 2013 9:33:29 AM	INFO    Embedded query tuner trace is ON; QT trace path = TRACE_PATH; QT trace current file = TRACE_CURRENT_FILE; QT trace file maximum size = 20; QT trace file limit = TRACE_FILE_LIMIT; QT trace file prefix = qtTrace; QT trace file suffix = .trc; current OS temporary directory = C:\Program Files (x86)\IBM\OPM/work

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
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.

Resources

Learn

Discuss

  • Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


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.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=950756
ArticleTitle=Tuning queries and workloads from InfoSphere Optim Performance Manager
publish-date=10312013