An alternate index is a secondary indexing mechanism that allows additional ways to access a Key-Sequenced Data Set (KSDS). This enhances data retrieval by providing alternate keys to access the data, thereby allowing queries to use different search criteria other than the primary key.

What is Alternate Key?

There are scenarios where we need to access the same record using the data that is not part of the primary keyfrom the VSAM file. For example, let us assume the VSAM KSDS file contains the employee records. The record structure as follows -

01 EMPL-REC.
	05 EMPL-ID        	PIC X(05).   -> primary key
	05 EMPL-NAME      	PIC X(20).
	05 EMPL-SAL       	PIC 9(07).
	05 EMPL-DEPT-ID   	PIC X(05).
	05 EMPL-DEPT-NAME   PIC X(10).

The data normally retrieved by using the EMPL-ID which is the primary key. There are scenarios where the employee records need to be retrieved by using the other fields of the recods (assume it is department). In this case, reading all employee records using EMP-ID and check its matched with the department needed is a lengthy process. So they have developed a concept for this scenario to retrieve records using other fields of the records (in this case, EMPL-DEPT-ID) as other key (not a primary key). The key(EMPL-DEPT-ID) other than the primary key (EMPL-ID) is called an alternative or secondary key.

The alternate key is used to retrieve the data from the file without using the primary key. Alternate key is same like primary key.

What is Alternate Index (AIX)?

The index built for the alternate key is called the Alternate Index (AIX). AIX contains index entries that are organized by the alternate key sorting order (ascending/descending) of its associated data records. AIX defines over a KSDS or ESDS cluster. AIX is not allowed to define on a reusable cluster, a fixed or variable-length RRDS, an extended ESDS, a catalog, another AIX, an LDS, or a non-VSAM file.

Notes -

• A unique index is defined for each alternate key.
• One or more AIXs can be created on the file.
• An AIX reduces redundancy, and no need to keep a separate dataset for different views.
• The AIX records are updated automatically when the base cluster is updated.
• AIXs do not support a reusable base cluster. So NOREUSE should be coded.

Advantages -

• It can be defined on both KSDS and ESDS.
• Reduces data redundancy.
• Can have duplicates.
• Easy to define using IDCAMS.
• Allows files to be accessed both sequentially and randomly.

Disadvantages -

• Reduce the performance.
• Complex to update logic.

Components -

The alternate index has three components, and those are -

• An alternate index cluster - represents the AIX as a key-sequenced cluster.
• DATA Component - represents the AIX's data component.
• INDEX Component - represents the AIX's index component.

Defining AIX -

Defining an AIX has three steps, and those are -

Step	Description
DEFINE ALTERNATEINDEX	Defines the AIX components (Cluster, DATA, and INDEX) and their catalog entries. At this point, an alternate index does not have any link with the base cluster.
DEFINE PATH	Defines the path between the AIX and base cluster for faster access to the file for retrieving the data. Entries represent the base cluster and AIX in the same catalog.
BUILD INDEX	Build an index for the alternate key by verifying the records with the primary index and creating the corresponding alternate index for each primary index.

---