Use IBM InfoSphere Optim Query Workload Tuner 3.1.1 to tune statements in DB2 for Linux, UNIX, and Windows, and DB2 for z/OS that reference session tables

IBM® InfoSphere® Optim® Query Workload Tuner (OQWT) 3.1.1 can tune statements for IBM DB2 for Linux®, UNIX®, and Windows®, and IBM DB2® for z/OS®. This tutorial describes how to use OQWT to tune a statement that accesses one or more session tables. Two methods are presented on how to set up the database environment for the session table such that OQWT 3.1.1 can tune statements using the table. Examples are provided for a script that is required to set up the environment, including example snapshots of the output and functionality of the applicable OQWT tuning features.

Share:

Daniel Zilio (zilio@ca.ibm.com), Senior Software Developer, IBM China

Author photo of Daniel ZilioDaniel Zilio is a Senior Software Developer in the IBM InfoSphere Optim Query Workload Tuner team. Previously he was on the IBM DB2 for Linux, UNIX, and Windows Optimizer and Autonomic Computing teams. He has worked on DB design algorithms, explain, DB simulation, self-tuning memory management, XML design selection, automatic statistics collection, the data mart advisor, workload statistical views advisor, collect actuals feature, workload index advisor, workload statistics advisor, access plan comparison, and what-if index analysis.



Cliff Leung (cleung@us.ibm.com), Senior Technical Staff Member, IBM  

Author Photo LeungCliff Leung is a Senior Technical Staff Member in the IBM Information Management organization at Silicon Valley Laboratory, San Jose. Cliff is the chief architect of the IBM InfoSphere Optim Query Tuner product since 2008, and is responsible for the overall product direction and strategy. Cliff has extensive experience in query compilation, optimization, and performance areas.



Gao Zhong Liang (lianggz@cn.ibm.com), Software Engineer, IBM China

Author photo of Gao Zhong LiangGao Zhong Liang is a Software Engineer in the IBM InfoSphere Optim Query Workload Tuner team.



Guo Bin Zhao (zhaoguob@cn.ibm.com), Software Engineer, IBM China

Author photo of Guo Bin ZhaoGuo Bin Zhao is a Software Engineer in the IBM InfoSphere Optim Query Workload Tuner team. He works on the core function part, including explainer, parser, configuration, and Workload Control Center components.



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

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



Bian Li (libian@cn.ibm.com), Software Engineer, IBM China

Author photo of Bian LiBian Li is a Software Engineer in the IBM InfoSphere Optim Query Workload Tuner team.



Xin Wu (xinwu@us.ibm.com), Software Engineer, IBM China

Author photo of Xin WuXin Wu is a Software Developer in the IBM InfoSphere Optim Query Workload Tuner team.



Rui Yang (yruicdl@cn.ibm.com), Software Engineer, IBM China

Author photo of Rui YangRui Yang is a Software Engineer in the IBM InfoSphere Optim Query Workload Tuner team. He has worked on Access Plan Explorer, Workload Control Center, Workload Index Advisor, Capture from Explain tables/Event Monitor tables. Previously he was on the IBM DB2 Java Common Access team.



08 November 2012

About this tutorial

IBM InfoSphere Optim Query Workload Tuner (OQWT) 3.1.1 offers features that allow statement tuning for IBM DB2 for Linux, UNIX, and Windows, and IBM DB2 for z/OS connections. This tutorial describes how you would use OQWT to tune a statement in OQWT that accesses one or more session tables (or global temporary tables).

For OQWT to be able to explain and tune a statement that accesses one or more session tables, the session table must be made active under the same database connection used by OQWT in running its tuning features. When a session table is active, the query tuning features can explain the statement with this table, and make use of the explain information to drive the tuning features.

To make the session table active, you must execute a script that will set up the session table environment. This script can include the creation of a session table, the DECLARE of the session table, addition of rows to the session table, as well as any other statements that will modify the session table to a state needed to exist for the statement in question to be tuned.

The created temp table (CGTT) and user declared global temp table (DGTT) are the two types of session (or temp) tables in DB2. If the temp table setup does not include a CREATE statement to make the temp table, and instead used only a DECLARE statement to make the temp table, then the temp table would be a DGTT. In the script to set up the temp table, one is only needed to have the DECLARE statement in the script. Note that OQWT has the same features accessible to statements using temp tables that are CGTT or DGTT. These features are shown in this tutorial and apply to statements on both types of tables. There are some differences between invoking OQWT when a statement contains a DGTT instead of a CGTT for DB2 for z/OS, which will be pointed out in the following sections.

An example of a script to set up the session table's state is shown in Listing 1.

Listing 1. Script to set up session table state
declare global temporary table SESSION.GTT2 (c1 integer)
insert into SESSION.GTT2 values(100)

Listing 2 shows an example of a script to set up a CGTT.

Listing 2. Script to set up CGTT
create global temporary table SESSION.GTT1 (c1 integer)
declare global temporary table SESSION.GTT1 (c1 integer)
insert into SESSION.GTT1 values(100);

Listing 3 shows an example of a statement using the temp table, where SESSION.GTT2 is joined with a base table ZILIO.ZZT.

Listing 3. Statement using temp table
select * from SESSION.GTT2 as X, zilio.zzt where X.c1 = zzt.a

This tutorial will describe how you must make the session tables(s) active for OQWT to tune statements using the table. The script to make the DGTT will be used in the examples provided. Note that the script can be an even more complex DB2 statement script such as setting up multiple temp tables, or setting up registers, and so on.

Once the script is executed, the statement can then be tuned using the OQWT features.

You can use the following two ways to run the script to set up the session table states.

  1. Run the script under the Integrated Query Editor (or SQL Script editor).
  2. Run the script from the OQWT Query Tuner Workflow Assistant.

In the sections that follow, you will first see how the script can be executed from the SQL Script editor, along with providing examples of the query tuning features that can be used on a statement accessing one or more session tables. The second section will show how to execute the script to set up the session table state from the OQWT Workflow Assistant, along with more examples of executing the query tuning features on a statement accessing one or more session tables.

The query tuner features that can be used with a statement accessing one or more session tables include the following.

  • Access plan graph
  • Access plan explorer
  • Test candidate index
  • Visual plan hint
  • Compare access plans
  • Query tuning report

It should be noted that the examples mostly use a DB2 for Linux, UNIX, and Windows connection. Similar functionality is available with a DB2 for z/OS connection. Examples are also provided in cases where OQWT functionality for DB2 for z/OS differs from features for DB2 for Linux, UNIX, and Windows.

Information on OQWT can be found in the Resources section.

System requirements

You can find the system requirements for OQWT 3.1.1 in the Resources section.

The OQWT client can be installed on a machine with the Linux and Windows versions defined in the system requirements.

You can activate a license for InfoSphere OQWT, Version 3.1.1 on the following releases of DB2.

  • DB2 Enterprise Server Edition 9.1 Fix Pack 8 and later.
  • DB2 Enterprise Server Edition 9.5 Fix Pack 1 and later.
  • DB2 Enterprise Server Edition 9.7 and later.
  • DB2 pureScale Feature for Enterprise Server Edition 9.8 and later.
  • DB2 10.1 Enterprise Server Edition for Linux, UNIX, and Windows.
  • DB2 for z/OS 8.1 and later.
  • DB2 for z/OS 9.1 and later.
  • DB2 for z/OS 10.1 and later.

Tuning a statement from the integrated query editor (SQL script editor)

In this section, you will learn how to execute the script to set up session tables from the SQL script editor. You will also see examples of running the applicable query tuning features on a statement involving the session tables.

Note that this method of executing the script in the SQL script editor can be used for DB2 for Linux, UNIX, and Windows connections for statements using CGTT and DGTT session tables, but for DB2 for z/OS connections, this method cannot be used for statements with DGTT session tables.

  1. Start the SQL script editor by right clicking the DB name (from Data Source Explorer), and then select New SQL Script, as shown in Figure 1.
    Figure 1. Example of launching the SQL script
    The example shows how to launch the SQL Script Editor
  2. In the SQL script editor, the script needs to be copied or entered into the editor. Also, under the Configuration tab for the SQL script editor, you must clear the Open new connection when script is run check box. Clearing this option will mean that once the script is executed, the connection where the session table state is set will be used to explain and tune a statement using that session table.
  3. To execute the script to set up the session table, highlight all of the rows of the script and right click them, then select Run SQL as shown in Figure 2.
    Figure 2. Example of running SQL from the SQL Editor
    The example shows how to run SQL from the SQL script Editor
  4. When the statements are run, the SQL Results tab will display the status of the statements in the script, as shown in Figure 3.
    Figure 3. Example of the status of running SQL from the SQL Editor
    The example shows the status of running SQL from the SQL script Editor
  5. When the script is executed properly, you can then highlight a statement, right-click it, and then select Start Tuning to launch the OQWT Query Tuner Workflow Assistant, as shown in Figure 4.
    Figure 4. Example of the query tuning launch from the SQL Editor
    The example shows how to launch query tuning from the SQL script Editor
    In this example, you use the statement with the DGTT (SESSION.GTT2) that is joined with a regular base table (ZILIO.ZZT).
    select * from SESSION.GTT2 as X, zilio.zzt where X.c1 = zzt.a
  6. When the Query Tuner Workflow Assistant is launched, you will see the statement to be tuned in the Query Text area of the Invoke view. To run the query tuner features, click the Select What to Run button, as shown in Figure 5.
    Figure 5. Example of the query tuning Invoke view
    The example shows the Invoke view of the Query Tuner Workflow Assistant
  7. You can now select which tuning features to run. As shown in Figure 6, for this example, all features are selected to run. To run these features, click OK.
    Figure 6. Example of the Select Tuning Activities window
    The example shows the Select Tuning Activities window
    For DB2 for z/OS, the screen is similar, as shown in Figure 7. Just like for DB2 for Linux, UNIX, and Windows, you can click Select All and then click OK.
    Figure 7. Example of the SELECT ALL option for DB2 for z/OS
    The example shows the Select All option in the Select Tuning Activities window
  8. If a statement contains temp tables, OQWT will return a warning that no single query advisors can be executed on this statement, as shown in Figure 8.
    Figure 8. Example of the query tuner warning for temporary table use
    The example shows the query tuner warning for temporary table use
    However, other query tuner features are run and are shown in subsequent figures and descriptions.
  9. The access plan graph will visually display the query access plan as shown in Figure 9. The graph will include the session table and operators that access it.
    Figure 9. Example of the access plan graph with a session table
    The example shows the access plan graph with temporary table use
    Note that for OQWT with DB2 for z/OS, the features allowed for single query tuning on statements with sessions tables are slightly different, The OQWT features for DB2 for z/OS are shown by the highlighted list in the Invoke view provided in Figure 10.
    Figure 10. Example of the tuning features listed for DB2 for z/OS
    The example shows the list of tuning features for DB2 for z/OS
    The added query tuning features that are also allowed with a DB2 for z/OS connection for a statement using session tables include query formatting, Access Path Reports, and Capture Query Environment features.
  10. For OQWT with DB2 for z/OS, the formatted query option can be selected as shown in Figure 11.
    Figure 11. Example of the query formatter
    The example shows the query formatter
    For OQWT with DB2 for Linux, UNIX, and Windows, the formatted query option is not selectable with statements using session tables. In the formatted query from DB2 for z/OS, each select column, table in the FROM clause, predicate, order by column, and group by column is shown in a separate line with appropriate indentation. For each table, the annotated information for that table includes the CARDF, QUALIFIED_ROWS and NPAGESF for that table. For each predicate, the annotated information for the predicate includes the COLCARDF, the MAX_FREQ and the filter factor (FF).
  11. Another query tuner feature that can be used with a statement accessing session tables is the access plan explorer, as shown in Figure 12.
    Figure 12. Example of the access plan explorer
    The example shows the access plan explorer
    This will present a tabular form of the explain and include access to the session tables in the statement.
  12. The details of the tuning will also be viewable by selecting the Open Summary Report option as shown in Figure 13.
    Figure 13. Example of the Summary Report
    The example shows the Summary Report
    This report includes details on the tables accessed that include the session tables used in the tuned statement.
  13. Additionally, for a DB2 for z/OS connection, the Access Path Reports feature will provide information for statements that include session tables. When selecting this option from the Invoke view of the Query Tuner workflow assistant, you will be prompted on what to include in the report, as shown in Figure 14.
    Figure 14. Example of the selection for Access Path Reports
    The example shows the selection for Access Path Reports
  14. When you select OK, you can see the access path reports for the plan in DB2 for z/OS, as shown in Figure 15.
    Figure 15. Example of the Summary Report for DB2 for z/OS
    The example shows the Summary Report for DB2 for zOS
    The reports will include items such as tables, indexes, or predicates operating on all tables including the session table.
  15. Another feature that is available is the Optimization Profile feature. You can launch it by selecting Edit Optimization Profile, as shown in Figure 16.
    Figure 16. Example of the Optimization Profile feature
    The example shows the Optimization Profile feature
  16. Use the Test Candidate Index feature for statements using session tables. In the Query Tuner Workflow Assistant, launch the Test Candidate Indexes feature from the Review view of the Query Tuner Workflow Assistant after obtaining the access plan graph and explorer. You could also directly select the Test Candidate Indexes option instead of the Select What to Run button, as shown in Figure 17.
    Figure 17. Example of the launch of the Test Candidate Indexes feature
    The example shows the launch of the Test Candidate Indexes feature
  17. In the Test Candidate Indexes view, you can select Add Index and enter the table to index as well as the virtual index name, creator, and index keys, as shown in Figure 18.
    Figure 18. Example of the Test Candidate Indexes feature
    The example shows the Test Candidate Indexes feature
    Virtual indexes can be added to base tables, MQTs, and CGTTs. Note that if you select the DGTT table, then you will not be able to select the index keys, and thus will not be able to add new indexes on these tables when using DB2 for Linux, UNIX, and Windows, or DB2 for z/OS connections.
  18. Once the indexes are added in this view, you can click the Test Candidate Indexes button to explain the statement such that the virtual indexes are considered by the optimizer along with the existing indexes, as shown in Figure 19.
    Figure 19. Example of the index listing in the Test Candidate Indexes feature
    The example shows the index listing in the Test Candidate Indexes feature
  19. You are prompted to change the statistics for these virtual indexes. You can choose the defaults of -1.0 for statistics, as shown in Figure 20, for a DB2 for Linux, UNIX, and Windows connection.
    Figure 20. Example of the index options window in the Test Candidate Indexes feature
    The example shows the index options window in the Test Candidate Indexes feature
    Once the statistics have been decided, you can select OK to execute the query explain.
  20. Note that when using the Test Candidate Indexes option with a DB2 for z/OS connection, the sequential pages, density, MINPCTUSED, and the indicator to allow reverse scans are not applicable, as shown in Figure 21.
    Figure 21. Example of the index options window in the Test Candidate Indexes feature for DB2 for z/OS
    The example shows the index options window in the Test Candidate Indexes feature for DB2 for z/OS
  21. Once the optimizer is executed, the access plan graph that results is shown in Figure 22.
    Figure 22. Example of the access plan graph from the Test Candidate Indexes feature
    The example shows the access plan graph from the Test Candidate Indexes feature
    Note that the resulting example shows the plan that uses the virtual indexes on the base table, ZZT.
  22. Once the access plan graph is shown when running the Test Candidate Indexes feature, you can select the Compare Access Plan Graphs option, as shown in Figure 23.
    Figure 23. Example of launching the access plan graph comparison feature
    The example shows the launching of the access plan graph comparison feature
    This option allows the access plan graph that was just generated to be compared to any other plan for that statement.
  23. You can compare the access plan graph from the initial explain, before Test Candidate Indexes was done, by selecting the Analysis Result 1 on the left-hand side, as shown in Figure 24.
    Figure 24. Example of selection of plans for the access plan graph comparison feature
    The example shows the selection of plans for the access plan graph comparison feature
  24. You can compare this to the access plan graph from the previous Test Candidate Indexes run by selecting Test Candidate Indexes 2 on the right-hand side of the view. Once these plans are set, you can select the Compare button.
  25. The access plan graph comparison will be shown as a side-by-side visual comparison, and will also be explained textually in the Differences Tree area, as shown in Figure 25.
    Figure 25. Example of the access plan graph comparison output
    The example shows the access plan graph comparison output

Tuning a statement from the Query Tuner Workflow Assistant

The other way to execute a script to set up the environment with one or more session tables before tuning a statement is by using the Query Tuner Workflow Assistant. With this method, you must execute the script one line at a time from the Query Text editor.

Note: this method of executing the script in the Query Tuner Workflow Assistant can be used for DB2 for Linux, UNIX, and Windows, and DB2 for z/OS connections for statements using CGTT and DGTT session tables.

  1. Launch the Query Tuner Workflow Assistant by right clicking the database name in the Data Source explorer, select Analyze and Tune, and then click Start Tuning, as shown in Figure 26.
    Figure 26. Example of the launching the query tuner from the Data Source Explorer
    The example shows launching the query tuner from the Data Source Explorer
  2. Click Input Text. Enter the first statement of the script in the Query Text editor area, and then click Invoke Advisors and Tools, as shown in Figure 27.
    Figure 27. Example of the Capture view in the Query Tuner Workflow Assistant
    The example shows the Capture view in the Query Tuner Workflow Assistant
  3. Enter each line of the script, one at a time, in the Query Text window. For each line in the script, click the Run SQL button in the Invoke view. Figure 28 shows an example of executing the Run SQL for each line of the script.
    Figure 28. Example of running SQL from the Query Tuner Workflow Assistant
    The example shows running SQL from the Query Tuner Workflow Assistant
  4. You will see the SQL results with the status of the statement, as shown in Figure 29.
    Figure 29. Example of the output of running SQL from the Query Tuner Workflow Assistant
    The example shows the output of running SQL from the Query Tuner Workflow Assistant
  5. Figure 30 shows the second line of the same script used in the previous section, where you must again select the Run SQL button.
    Figure 30. Example of running the second line of SQL from the Query Tuner Workflow Assistant
    The example shows running of the second line of SQL from the Query Tuner Workflow Assistant
  6. After all of the lines of the script to set up the session tables are run, you can tune the statement that accesses the table as in the previous section. For example, Figure 31 shows how you would enter the join query and select the Select What to Run button, and choose the tuning features to run in the pop-up menu.
    Figure 31. Example of launching query tuning from the Query Tuner Workflow Assistant
    The example shows launching query tuning from the Query Tuner Workflow Assistant
    At this point, the same tuning features are accessible as was shown in the previous section.

Conclusion

In this tutorial, you were shown how to set up a database environment for the session table such that IBM InfoSphere OQWT 3.1.1 can tune statements using the table. You learned two methods for creating a script to set up the environment for tuning on either DB2 for Linux, UNIX, and Windows, and DB2 for z/OS servers. Examples were provided for the script that is required to set up the environment, including the output and functionality of the applicable OQWT tuning features.

Acknowledgements

We would like to thank Kendrick Ren for his input to the tutorial.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

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=844222
ArticleTitle=Use IBM InfoSphere Optim Query Workload Tuner 3.1.1 to tune statements in DB2 for Linux, UNIX, and Windows, and DB2 for z/OS that reference session tables
publish-date=11082012