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.
- Run the script under the Integrated Query Editor (or SQL Script editor).
- 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.
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.
- 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
- 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.
- 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
- 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
- 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
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
- 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
- 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
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
- 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
However, other query tuner features are run and are shown in subsequent figures and descriptions.
- 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
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 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.
- 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
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).
- Another query tuner feature that can be used with a statement
accessing session tables is the access plan explorer, as shown in
Figure 12. Example of the access plan explorer
This will present a tabular form of the explain and include access to the session tables in the statement.
- The details of the tuning will also be viewable by selecting the
Open Summary Report option as shown in Figure
Figure 13. Example of the Summary Report
This report includes details on the tables accessed that include the session tables used in the tuned statement.
- 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
- 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 reports will include items such as tables, indexes, or predicates operating on all tables including the session table.
- Another feature that is available is the Optimization
Profile feature. You can launch it by selecting
Edit Optimization Profile, as shown in Figure
Figure 16. Example of the Optimization Profile feature
- 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
Figure 17. Example of the launch of the Test Candidate Indexes feature
- 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
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.
- 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
- 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
Figure 20. Example of 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.
- 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
- 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
Note that the resulting example shows the plan that uses the virtual indexes on the base table, ZZT.
- 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
This option allows the access plan graph that was just generated to be compared to any other plan for that statement.
- 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
- 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.
- 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
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.
- 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
- 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
Figure 27. Example of the Capture view in the Query Tuner Workflow Assistant
- 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
- 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
- Figure 30 shows the second line of the same script used in the
previous section, where you must again select the Run
Figure 30. Example of running the second line of SQL from the Query Tuner Workflow Assistant
- 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
At this point, the same tuning features are accessible as was shown in the previous section.
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.
We would like to thank Kendrick Ren for his input to the tutorial.
- Learn more about Optim Query Workload Tuner from the Optim Query Workload Tuner Infocenter.
- Learn more about OQWT 3.1.1 system requirements.
- Learn more about DB2 for Linux, UNIX, and Windows from the IBM DB2 for Linux, UNIX, and Windows Information Center.
- Review the IBM InfoSphere Optim Query Workload Tuner, Version 3.1.1 release notes.
- Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
- Stay current with developerWorks technical events and webcasts.
- Follow developerWorks on Twitter.
Get products and technologies
- Build your next development project with IBM trial software, available for download directly from developerWorks.