Displaying index advisor information

You can display index advisor information from the optimizer using System i® Navigator.

System i Navigator displays information found in the QSYS2/SYSIXADV system table.

To display index advisor information, follow these steps:

  1. In the System i Navigator window, expand the system that you want to use.
  2. Expand Databases.
  3. Right-click the database that you want to work with and select Index Advisor > Index Advisor.

You can also find index advisor information for a specific schema or a specific table by right-clicking on a schema or table object.

Once you have displayed the information, you have several options. You can create an index from the list, remove the index advised from the list, or clear the list entirely. You can also right-click on an index and select Show SQL, launching a Run SQL Scripts session with the index creation statement. Finally, you can right-click on an advised index and select Show Statements. With additional information automatically provided in the advised index filter for the Plan Cache search, the resulting SQL statements shown will be a better match to the original queries that generated that specific index advice.

Depending on if you are viewing the index advice at the database level or the schema level your list could be large. Once you have the list displayed, follow these steps to subset your list:

  1. Go to the View menu option, and select Customize this view > Include ....
  2. Enter the information you would like to filter the list by.
  3. Press the OK button to get the refreshed list of index advice.