As mentioned in the August 20th issue of the newsletter, the iSeries Navigator toolset was enhanced in V5R3 with a Show Indexes task. This new task, commonly referred to as Index Evaluator, provides more details on which indexes are being used by the DB2 query optimizer to improve the performance of your queries and SQL requests.

Starting with V5R4, this same information can also be retrieved via a program using a DB2 catalog view. SYSPARTITIONINDEXSTAT in library QSYS2 is a view that externalizes this index usage. It contains a row of data for every index that's built over a table or physical file member. The term index in this case refers to every index object that is available to the DB2 for i query optimizer when building a query access plan. Included in this index list are all the indexes associated with SQL indexes, keyed logical files, and keyed physical files, as well as the internal indexes used by DB2 to enforce primary key, foreign key, and unique key constraints.

With this information available via an SQL view, it's quite simple to create utilities or reports so you can look for rarely-used indexes. A stored procedure-based utility is included below to show you how easy it is to leverage the data in the catalog view in this way. This utility returns a list (actually a result set) of those indexes in a schema (library) that have not been used in a specified number of months. A stored procedure is used for this utility, but is not required--the view can also be directly accessed through any interface (IBM DB2 Web Query, STRSQL, etc).

The target schema is passed into the stored procedure with the ischema parameter. The imonths_notused parameter is used for the months search criteria. The cursor (ixlist) used to return the procedure result set employs a common table expression to break the index search into two steps.

CREATE PROCEDURE Unused_Indexes 
           (IN ischema VARCHAR(10),
             IN imonths_notused INT)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
 DECLARE ixlist CURSOR WITH RETURN FOR 
   WITH candidate_list as (
          SELECT 
            CURRENT DATE - DATE(last_query_use) AS months_lastqry,
	CURRENT DATE - DATE(last_statistics_use) AS months_laststats,
	CURRENT DATE - DATE(last_used_timestamp) AS months_lastused,
	index_schema, index_name, 
            index_type,
	last_query_use, 
            last_statistics_use,
	last_used_timestamp, 
	query_use_count,
	query_statistics_count,
	days_used_count
         FROM qsys2.syspartitionindexstat 
         WHERE system_table_schema=ischema  
                        AND unique > 1 )
  SELECT
	index_schema, index_name, 
            index_type,
	last_query_use,
	last_statistics_use,
	last_used_timestamp,
	query_use_count,
	query_statistics_count,
	days_used_count
  FROM candidate_list
  WHERE (months_lastqry > imonths_notused*100 OR months_lastqry IS NULL)
    AND    (months_laststats > imonths_notused*100 OR months_laststats IS NULL)
    AND    (months_lastused > imonths_notused*100 OR months_lastused IS NULL)
  ORDER BY MIN( coalesce(months_lastqry,99999), 
                                 coalesce(months_laststats,99999), 
                                 coalesce(months_lastused,99999)) DESC;

    OPEN ixlist;
END;

In the first step, the table expression collects the list of candidate indexes by the table expression by selecting only those indexes defined over tables in the specified schema (system_table_name = ischema). This utility does not include any unique indexes (UNIQUE>1) in the candidate list since they are being used by DB2 to enforce unique keys regardless of whether or not the query optimizer uses them.

The main part of the Select statement then takes the candidate indexes and determines which indexes have not been used in the number of months specified by imonths_notused. This parameter value is multiplied by 100 because the subtraction of two date values in the first step results in a date duration value. You can also alter the utility or report to just compare the last-used columns against a specific date value. The program then sorts that data to return those indexes at the top of the list that have existed the longest without being used. The result set is then returned to the caller by opening the ixlist cursor and leaving the cursor open.

Here's an example of how to invoke the procedure to return those indexes not used in the last 2 months:

CALL unused_indexes('MYLIB', 2)

The output returned by the stored procedure would look something like this:

MYLIB  SLFMON1     LOGICAL	     -	         …
MYLIB  FK1         FOREIGN KEY 04/09/08 14:17  …
IXLIB  D2IX2       INDEX       06/11/08 17:33  …    

More documentation on the SYSPARTITIONINDEXSTAT catalog view can be found in the DB2 for i SQL Reference.