Explain Alternate Indexes in IBM Mainframes

47 / 60

VSAMadvanced

Alternate Indexes

Understand how alternate indexes let a VSAM base cluster be accessed by a second, non-primary key without duplicating the underlying data.

An alternate index (AIX) lets you look up records in a VSAM base cluster by a field other than its primary key, without physically duplicating the data itself — instead, DB2-style, it maintains a separate index structure mapping alternate key values back to the base cluster's primary key (or RBA), and this is consistently one of the more advanced VSAM topics interviewers use to distinguish genuinely experienced candidates.

An alternate index is a second index card catalog at the same library, this one organized by author's last name instead of by call number, both catalogs pointing back to the exact same physical books on the exact same shelves — no book is ever duplicated, but every time a book is reshelved, both catalogs must be updated to stay accurate.

Key Concepts

The classic motivating example: a customer master KSDS keyed by customer ID, but the business also needs to look customers up by, say, phone number or account-opening date. Rather than maintaining a second full copy of the customer file physically sorted by phone number (which would require synchronizing two copies on every update), you define an alternate index on the phone number field, and VSAM handles keeping that index synchronized automatically as the base cluster changes.

Accessing the base cluster through an alternate index requires a PATH — a catalog entry connecting the AIX to its base cluster, which the COBOL program's SELECT clause references (or references the AIX directly, depending on setup) to read records via the alternate key instead of the primary one. Alternate indexes can also be defined as non-unique, allowing multiple base records to share the same alternate key value (like multiple customers with the same last name), in which case reading via that key returns each matching record in turn rather than a single unambiguous hit.

A sharp interview answer highlights the operational cost side of this convenience: every alternate index defined on a base cluster adds overhead to every insert, update, and delete against that cluster, since VSAM must maintain the AIX's synchronization automatically — much like the write-cost tradeoff of DB2 secondary indexes, and a direct analogy interviewers appreciate when drawn explicitly.