238 SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
The Index and Statistics Advisor window (Figure 9-7) shows you all the recommended
indexes and statistic columns. If you click Create, you can easily create the suggested
indexes. All key columns are listed in the desired sequence.
Figure 9-7 Index Advisor showing the recommended indexes
9.2 Optimization of your SQL statements
It is not possible to force the optimizer to use a particular index, but you can affect the
optimizer’s decision by coding the SQL select statement in different ways. In the following
section, we show you some ways that you can affect the optimizer.
9.2.1 Avoid using logical files in your select statements
It is a common misunderstanding that, by specifying logical files in SQL statements, the query
optimizer can be forced to choose this index. This is not true. The specified index can be
selected if it meets all the requirements for the best access path. But the query optimizer can
choose any other index or decide even on doing a table scan as well.
If you specify a keyed logical file in an SQL select statement, the optimizer takes only the
column selection, join information, and select/omit clauses, and rewrites the query. For each
of these joined tables, a specific index is determined.
There is another point to avoid specifying logical files in an SQL statement. A select
statement that contains logical files is rerouted and executed by the CQE and does not
benefit from the enhancements of the new SQL Query Engine (SQE). The cost of rerouting
might cause an overhead of up to 10 to 15% in the query optimization time.
Chapter 9. Tips to prevent further database performance problems 239
We illustrate this with an example. Suppose that we have a base table ORDHDR that contains
all order header information. In addition to a primary key constraint, two logical files are
created.
Example 9-4 show the DDS description of the keyed logical file ORDHDRL1, with the key
fields ORHDTE = Order Date and ORHNBR = Order Number.
Example 9-4 Logical file ORDHDRL1
A R ORDHDRF PFILE(ORDHDR)
*
A K ORHDTE
A K ORHNBR
Example 9-5 shows the DDS description of the second logical file ORDHDRL2, with the key
fields ORHDLY = Delivery Date in descending order, CUSNBR = Customer Number, and
ORHNBR = Order Number.
Example 9-5 Logical file ORDHDRL2
A R ORDHDRF PFILE(ORDHDR)
*
A K ORHDLY DESCEND
A K CUSNBR
A K ORHNBR
Now we want to select all orders with the order date 11 March 2005. In the first case, we
perform the select using the logical file ORDHDRL2, and in the second case, we use the
physical file.

Get SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.