DB2 SQL Query Performance Tuning (3 of 3)

Tune your SQL query for better performance and save CPU utilization.

6 Ideas to Tune SQL Query

Check the predicates. Whether it is one query or multiple queries in a program, check every predicate in every query to ensure that they are indexable, stage 1, and as simple and straightforward as possible.
If there is a Distinct or Group By in the query, make sure it is needed and then look at the Explain to see if it is causing a sort to take place. If the Distinct or Group By is needed, maybe there is another way to rewrite the query to handle the issue of a duplicate that will not cause a sort.
Execute an Explain. In the Explain output, check the following: Are any tablespace scans occurring? Are any sorts occurring? If the query has a Union, Distinct, Group By, or Order By in it, does it need to be there?
When JOINS involved, what is the order of tables being processed? DB2 should be selecting the table that will be filtered the most as the starting table. If it is not selecting the table being filtered the most, then check the columns of the predicates and make sure there are enough statistics on these columns to help the optimizer. To determine which table is going to be filtered the most, you must know the values coming in at runtime. You can execute Select count(*) statements to figure this out.
All correlated subqueries should use an index, and if possible, they should process with the index only = yes. A correlated subquery is a subquery that contains a join to a column from the outer table.
Any nested loop join operations should have their tables processed using an index with matching columns. If the starting (composite) table is showing a tablespace scan, then this may not be much of an issue due to the fact that it will be scanned only one time. But for a joined table, any tablespace scans will cause that table to be scanned numerous times.