Warranted the best index for DB22017-01-19T11:40:36+00:00

Warranted the best index for DB2

How to maintain the best indexes on DB2  for z/OS?

Index Model is critical for DB2 performance

One of the most important factors in DB2 application performance is the index design. Poor index usage can ruin the application’s performance. Deciding which columns need indexing, and the order of concatenated keys, is a difficult task. Often it is pure guesswork, based on assumptions and the presence of certain columns in SQL.

Index analysis is a time-consuming and daunting task since it calls for reviewing individual SQL of an application for predicate usage and simultaneously take business priorities into account. A time intensive process because all applications with reference to the target table being indexed have to be reviewed and weighted accordingly. Typically, only experienced DBAs have the skills necessary to design indexes for large applications manually.

When many thousands of queries are involved, an automated Index Administrator system is the only way of ensuring smart indexes.


  • Improves performance of your DB2 systems by using the best index key columns

  • Checks if existing indexes are still relevant e. g. are there any overlapping indexes?

  • Compares the impact of new or virtual indexes on a table
  • Checks all SQL related to a single table, whether part of a workload analysis with captured SQL and real execution frequencies or non-executed SQL originating from an application that runs once a week for several hours

  • Cleans up the DB2 Catalog by discarding non-required indexes, “same” indexes or similar indexes


  • Extracts and scans SQL to identify which columns are accessed, by which SQL statements and how they are referenced
  • Identifies potential candidates automatically (OOX feature)

  • Sets weight factors to influence certain situations and applications in accordance with business constraints

  • Identifies references between applications and specific resources and vice-versa

What you can do