Get index recommendations with SQL Optimizer for DB2 z/OS

Using indexes can improve the performance of applications using the database. This is a pretty well known fact. However, determining which index, or combination of indexes that will give you the best performance used to be a difficult and time consuming task.  If you’re already using the SQL Optimizer for DB2 z/OS, you can take advantage of the new index recommendations option to help with this process.

Before we jump into how to use this new feature, I feel I should provide you with a little bit of background information. Using SQL Optimizer’s index recommendation feature you can determine if adding indexes will improve the performance for a single SQL statement. SQL Optimizer will generate some virtual indexes for the SQL statement to see if adding some real indexes will make the statement run faster. The virtual index sets generate a unique execution plan for the SQL statement without impacting database performance.

So, let’s look at an example. Below is a relatively simple SQL statement within Toad for DB2 z/OS’s SQL editor. Important note: SQL Optimizer for DB2 z/OS is only available with Toad for DB2 z/OS Xpert edition or Development Suite.

You can launch SQL Optimizer standalone from the Window’s Start Menu but I prefer to write my SQL within Toad and then simply click the tuning icon on the editor toolbar to launch SQL Optimizer. Toad will then pass the SQL directly into SQL Optimizer. The other advantage to launching SQL Optimizer directly from Toad is that in addition to the SQL, Toad also passes your connection information, too.

Toad will notify you that it is going to launch SQL Optimizer:

You’ll then see the splash screen displayed:

The first thing that SQL Optimizer does is to determine the execution plan for the SQL as it currently exists. This provides you with a baseline with which to compare:

Using SQL Optimizer, we have a couple of options. We can have it simply try to rewrite the SQL to see if there is an alternate way to write the SQL that will perform better, we can ask Optimizer to generate virtual indexes to see if adding an index will improve performance, or we can do both. To get the best possible performance, let’s select Rewrite and Index:

Depending on the complexity of your SQL and options you set for how many alternatives you’d like SQL Optimizer to provide, this step can take a few minutes.

Once SQL Optimizer has completed its analysis, it will present its findings compared to your original SQL. In our case, SQL Optimizer has found 8 alternative ways to write the SQL and 8 possible index combinations that might be faster than the original. At this point, there are only options. The plan cost on a couple certainly look promosing, but we don’t know for certain whether any will prove to be faster than our original.

The way to find out is to test run them. We can either selectively run alternates or index recommendations, or since I don’t have too many, I’ll have SQL Optimizer run them all. At this point, to test whether the indexes will prove to help, they must be physically created. Up until now, everything has been done with a virtual index. Once SQL Optimizer test runs the SQL, it will go back and clean up and remove all indexes that it has created. On the off chance that something should go wrong while running the tests (power outage, memory issue, etc), don’t worry. SQL Optimizer keeps track of the indexes that it has created and will always clean up behind itself even if there’s a problem. Just restart SQL Optimizer and it will remove any indexes it has created.

With our test case, SQL Optimizer has found 15 faster options, with index combination #5 producing the fastest run time. If we add the indexes recommended, we can reduce our run time from 6.26 seconds to .53 seconds:

Clicking on Index 5 will display the script we can run to create the necessary indexes:

Index recommendations will be available with SQL Optimizer for DB2 z/OS v5.5. SQL Optimizer for DB2 z/OS is available with the Toad for DB2 z/OS Xpert edition. You can learn more and download a trial copy here.