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.