So now that you've had some experience looking at the new Index Advisor in V5R4, you feel you're ready to act on some of the indexing advice. But because you don't want to test your indexing strategy "theories" on your production environment, you'd like a way to move the index advice to your test environment.

Here, I discuss a way that you can export and make use of the index advice from your production environment to help you confidently generate an implementation plan based on testing these theories out in your test environment. The key method for this will be to make use of the Export and Import capabilities in iSeries Navigator.

Advice Is Informative

First, a little refresher on the Index Advisor. IBM introduced this function in V5R4 as a way to capture the actual index advice as the optimizer is optimizing queries. The query optimizer performs this collection of advice in realtime without the need for starting any type of collection tool. What you may not know is that this advice is being kept in a DB2 table named SYSIXADV in the QSYS2 schema, so the advice is being persisted.

Because you can do a good job of reviewing database query implementation in your applications with tools such as Visual Explain among others, you may feel your production applications have been tuned pretty well. However, after reviewing the indexing advice from the Index Advisor, you're ready to take your indexing strategy to the next level.

For example, looking at the Index Advisor information for your application (Figure 1), you see several query tuning opportunities you may want to experiment with for SALES table. Of course, as stated earlier, it would not be good practice to experiment with these in your production environment, so you need to move this advice to your test environment.

Now to Make It Useful

The good news is that there is a convenient way to export this information from your production environment to your test environment. This is the Data Export feature within the Database function in iSeries Navigator.

The table that you'll use as the source for this export is SYSIXADV in the QSYS2 schema, which is where most of the system catalog tables reside. First, you need to navigate to this table within iSeries Navigator (Figure 2). Expand the Databases folder and then the Schemas folder. (Note: You may need to add the QSYS2 schema to your schema list by right-clicking the Schemas folder and choosing the Select Schemas to Display menu option.)

Now expand the QSYS2 schema folder and select the Tables folder. A list of tables appears in the right window. Find the SYSIXADV table and right-click it. Select the Data menu option, and then choose Export to display the export wizard dialog (Figure 3).

The first page shows the table information for the SYSIXADV table. You can use the defaults on this page and click Next. Now you're on the tab where you can select all the rows from the table or subset the rows (Figure 4). Because you're interested only in your application's index advice, you need to subset the data. So you select the SQL query radio button.

The edit box conveniently includes all the columns from the table, so you can quickly see which ones might be useful to help you subset the data. The application's schema (library) name is PRODAPPL, so you want to use that in a WHERE clause in this SQL query.

Once you scroll down to the bottom of the query, add the WHERE clause after the FROM QSYS2.SYSIXADV part of the statement. As Figure 4 shows, the WHERE clause will look like this:

WHERE TABLE_SCHEMA = 'PRODAPPL'

Even though the export wizard gives you access to all the columns via SQL statement, make sure you don't change the SQL to exclude any of the columns because you're going to import this into an SYSIXADV table in your test environment.

Notice that a couple of other helpful buttons are on this page of the wizard. The Check Syntax button helps you verify that the SQL statement changes you make are syntactically correct. If you click Preview Results, you can see the results from your query in a result window (Figure 5).

Once you're satisfied that the rows are the index advice rows you want to export, click Next. On this page (Figure 6), fill in the file name where you want to export the advised index rows to. Click Browse to display a standard file chooser dialog to help with this. Depending on how many rows you're exporting, you could navigate to your PC and save the rows, or you could use the IFS directory structure.

After entering the file name, click Next. The next page shows some settings to use when exporting the rows. You can leave them as the default values.

Now you've completed filling in the wizard pages and are ready to click Finish. Before you do this, however, note the Show Command button on the last page (not shown). If you click this button, a Run SQL Scripts dialog will appear (Figure 7) that contains the statements that can be used to automatically run this export. This is useful if you want to periodically keep track of the index advice being generated for your application.

Note that there are two statements. The first is a view created in QTEMP through which you'll make your selection. The view is needed because you're using a WHERE clause to subset the rows you want from the table. If you had wanted to export all rows from the SYSIXADV table, there wouldn't be a view statement in this script. The second statement is the Copy to Import File (CPYTOIMPF) command to do the export.

However, you're going to do a manual export, so you click Finish, and a status page of the wizard appears for you to make sure that the export completed successfully. This status page also includes the number of bytes exported.

Loading Up for the Test

Now you're ready to start the testing. Before you apply the production advice, make sure you clear out any old advice that may be left around in your test environment for this application. Because you have the same schema in your test environment, you can navigate to the PRODAPPL schema in iSeries Navigator, select the Index Advisor menu option, and choose Clear All Advised Indexes, which will clear out all the indexes.

The SYSIXADV table will continue to accumulate index advice. There is no automatic system pruning of the rows in this table, so you may want to periodically clear out the index advice for your application or for the whole database.

Now you're ready to load the advice. Navigate to the QSYS2 schema, expand the folder, and click the Tables folder. Find the QSYSIXADV table, right-click it, select the Data menu option, and then choose Import to display the import wizard (Figure 8).

On this first page, you need to find and fill in the file that you exported from the production environment. Use the Browse button to find the file. Because you took the default delimiters when you exported the file, you'll leave this the default too. Click Next.

Accept the defaults on the next three pages, and you'll get to a page with the table name you selected already filled in. After you verify the information, click Next. The next page shows options for dealing with the existing rows in the table. Because you want to insert only your new rows, make sure to leave the radio button selected for the Keep the existing rows option (Figure 9).

Now that you've completed filling in the import wizard's setup pages, you're ready to click Finish to perform the import. Before you do that, though, notice that there is a Show Command button on this page also, just as there was on the last setup page for the export wizard. When you click this button, a Run SQL Scripts dialog appears (Figure 10) that contains the statements that can be used to automatically run this import.

There are two steps in this script. The first step is for creating an error record file in case something is wrong with the data you are importing. You don't expect this to happen in your case. The second step is making use of the Copy from Import File (CPYFRMIMPF) command to do the import.

Now it's time to click Finish to complete the import. The status page of the import wizard appears (Figure 11) so you can ensure that the import completed successfully. This status page also includes the number of rows imported, which should match your results from the Preview Results function you performed in the export wizard.

Now for the Test

After you have the index advice imported into the SYSIXADV table, you can navigate to the PRODAPPL schema, right-click it, and select the Index Advisor menu option and then the CONDENSE ADVISED INDEXES menu option (Figure 12).

You want to use this menu option because it will take the index advice for this schema and condense it into fewer and more useful indexes than the raw Index Advisor information. Because your test schema has the same design as the production schema, you can begin to review the advised indexes and verify their usefulness in your indexing strategy.

There are many resources available that discuss all the considerations that go into evaluating which indexes are useful in your indexing strategy. Your analysis should include not only these advised indexes but also existing indexes. You can find a white paper on this at the DB2 for i5/OS website.

Common things to consider include sorting the list of advised indexes by number of times advised and creating the top N indexes that are expected to make a big difference. Other things to consider are (1) indexes advised on large tables where table scans are occurring or temp index builds are done, and (2) how long the queries run or how often the queries are run.

Once you've selected an advised index to experiment with, you can create the index by right-clicking the advised index and selecting the Create Index menu option. This will display the New Index dialog with all the information to create the index filled in except the index name (Figure 13).

Once you fill in the name, click OK to create the index. Continue until you have created all the indexes you want to use in this experiment. Then, start your application in your test environment and run it for a valid duration/workload.

You may need to iterate this process several times to find the most useful indexes to create. Here are examples of some of the tools you can use to verify the usefulness of these new indexes.

First, you can refresh the Condensed Index Advice list and verify that the counters for Number of Times Advised for Query Use did not increase for any of these indexes.

Next, you can navigate to the Tables folder in the schema, select a table you're interested in, and choose the Show Indexes menu option. There you should see the new indexes you just created, and the Query Use Count and Query Statistics Use Count columns should contain some information. You can see in the dialog that your new indexes are starting to be used.

You can also navigate to the SQL Plan Cache tool, bring up the queries for schema PRODAPPL, and view the queries run to see whether the query runtimes are improved from your query runtime baselines.

You could also use the Visual Explain tool to get a picture view of which indexes the queries are using.

Completing the Cycle

After you feel comfortable that the new indexes will improve your query response time, you can go back to the CONDENSE ADVISED INDEXES list, right-click an index in the list, and click Show SQL to get the DDL statement in a Run SQL Scripts dialog.

Now all that is left to do is put the index CREATE DDL statements into your change management control tool and have them scheduled for creation in your production environment.

One optional step is to clear the index advice on your production system for PRODAPPL in preparation for the new indexes being used in the production system. You'd do that similarly to how you cleared the index advice in your test environment before you started to test the indexes.

Good Indexing to You

As you can see, with the help of the export and import wizards, you can test your theories in your test environment to come up with some indexing strategy improvements. You may find other uses for the wizards as well.

After you've done the hard work of updating your indexing strategy, you can relax and return to monitoring the query environment mode. You can make use of the same tools in your production environment as you did earlier for monitoring and reviewing in your test environment.

Jim Flanagan is a senior software engineer at IBM in database development for DB2 for i5/OS in Rochester, Minnesota. He is the team leader of iSeries Navigator — Database.