![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
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.
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
13.
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
16.
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
17.
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
connection.
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
Table of contents
- About this tutorial
- Tuning a statement from the integrated query editor (SQL script editor)
- Tuning a statement from the Query Tuner Workflow Assistant
- Conclusion
- Resources
- About the authors
