• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Index Advisor, Part 2

    April 11, 2012 Paul Tuohy

    In part 1 of this series, I introduced you to the basic functionality of Index Advisor. In this article, we will take a look at more of the options and functionality available with Index Advisor for Systems Director Navigator for i.

    More Options

    Apart from the obvious information (table and schema names), the Index Advisor window also gives us some pertinent information, such as:

    • The advised key
    • The first and last time the index was advised
    • The number of times the index was advised
    • The longest and average execution times for the queries that generated the index advise
    • The estimated index creation time
    • The number of rows in the table the last time the index was advised
    • The reason the index was advised (row selection and/or ordering/grouping)

    Of course, the most important information is the execution time, since this is usually what causes a performance hit. The number of times the index was advised can also be a possible performance bottleneck.

    This is usually enough information to determine whether or not to go ahead and create an index (and if in doubt, create it anyway) but, at times, you may want to investigate a little further. For example, is the index being advised for one query statement or a number of query statements? If more than one statement, is the average query time higher for certain statements? What job/user initiated the query?

    Well, this information is available in the SQL Plan Cache, and the easiest way to get there is to right-click on the required line in the list and select Show Statements from the context menu to see the window shown in Figure 1. Patience is required here: it usually takes a while for the contents of the window to be populated. The contents of the SQL Plan Cache are a discussion for another day, but the primary information we are looking for (the statement) is readily available. We also have the execution times for each of the statements.

    Figure 1

    Figure 1: SQL Plan Cache. (Click graphic to enlarge.)

    Managing Index Advisor

    Within the Index Advisor window you have the usual controls for selecting the sequence of columns and which columns to display (F12), the sequence of items in the list (click column heading or View-Sort to sequence by multiple columns) or the Include option (F11) allows you to define the criteria for the indexes that should be displayed in the list, as shown in Figure 2.

    Figure 2: Include Option for Index Advisor.

    When you right-click on the Database name in System i Navigator, the Index Advisor option on the context menu provides three options to aid in managing the contents of the SYSIXADV table:

    1. Clear All Advised Indexes clears the contents of SYSIXADV. It is worthwhile considering this option if you want to start with a clean slate.
    2. Condense Advised Indexes can be useful when the index advisor has advised many different indexes for the same table. This option will condense the advised indexes.
    3. Prune Advised Indexes removes advised indexes for tables that no longer exist.

    Index Advisor in Systems Director Navigator for i

    Systems Director Navigator for i offers the same Index Advisor functionality as System i Navigator. You access Index Advisor by selecting the Databases task (in IBM i Management) and clicking on the database name. Then, from the drop-down Select Action list, select Index Advisor→Index Advisor and click on the Go button. Note that the sub-list for Index Advisor also offers the options to Clear All, Condense, and Prune Advised Indexes. Figures 3 and 4 show the selection of Index Advisor and the resulting list.

    Figure 3

    Figure 3: Selecting Index Advisor in Systems Director Navigator for i. (Click graphic to enlarge.)

    Figure 1

    Figure 4: Index Advisor in Systems Director Navigator for i. (Click graphic to enlarge.)

    The context menu for an item in the list (the double arrow after a name) provides the same context menu as we had in System i Navigator (as shown in Figure 4). We even have the option to view the SQL statements.

    Choose Your Poison

    Whether you opt to use System i Navigator, Systems Director Navigator for i, or running your own queries against SYSIXADV and coming up with your own automated or manual means of creating indexes; the important point is that you get the indexes created.

    DB2 on i is probably the most powerful database in existence, but only if we give it the required information to perform to its best.

    Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

    RELATED STORY

    Index Advisor, Part 1



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Abacus Solutions:  More affordable and flexible alternatives to deliver secondary workloads
    PowerTech:  Get PowerTech's PCI Compliance Kit to learn how PCI DSS applies to IBM i servers
    COMMON:  Join us at the 2012 Conference & Expo, May 6 - 9 in Anaheim, CA

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Oracle Unveils New BI Apps for Exalytics Machine IBM Launches Hybrid, Flexible Systems Into The Data Center

    Leave a Reply Cancel reply

Volume 12, Number 9 -- April 11, 2012
THIS ISSUE SPONSORED BY:

SEQUEL Software
Help/Systems
CNX

Table of Contents

  • Index Advisor, Part 2
  • Interpreted CL Members, Take 2
  • What Happened to My QFileSvr.400 Connection?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle