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 platform||Subdirectories in all_features||Description|
|LUW||IA||Create a query tuner stored procedure to generate index recommendation for single-query.|
|License||Activate a monitored database.|
|VPH||Not used by tuning in OPM.|
|WCC||Create query tuner stored procedure and meta tables to tune workloads.|
|ZOS||migration||Migrate query tuner metadata from V3.2 to V4.1 for DB2 Subsystem V8 to V11NFM.|
|Performance||Not used by tuning in OPM.|
|SCA||Not used by tuning in OPM.|
|V8NFM||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.
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|
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
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.
Figure 5. 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
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
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.
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
To open the logger
oqwtJobMgrLogger, click Open >
Logs in the OPM web console, shown in Figure 9:
Figure 9. 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.
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.
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.
- Attend a free developerWorks Live! briefing to get up-to-speed quickly on IBM products and tools as well as IT industry trends.
- Check out the IBM InfoSphere MDM Version 11.0 Information Center.
- Check out the IBM Cognos Business Intelligence Version 10.1.1 Information Center
- Get involved in the My developerWorks community. Connect with other developerWorks users while exploring the developer-driven blogs, forums, groups, and wikis.