Alternate Index (for Beginners)

What is Alternate Key?

The same record needs to be retrieved differently from the VSAM dataset based on the various requirements. For example, let us assume the VSAM KSDS file contains the employee records. The structure as follows -

	05 EMPL-ID        	PIC X(05).
	05 EMPL-NAME      	PIC X(20).
	05 EMPL-SAL       	PIC 9(07).
	05 EMPL-DEPT-ID   	PIC X(05).

In most cases, the data is retrieved by the EMPL-ID, which might be set as a primary key. There might be scenarios where the employee records need to be fetched using the department. In this case, reading all employee records and compare with the required department is a lengthy process. So they have developed a concept for this scenario to retrieve records using EMPL-DEPT-ID as a 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 the key used to retrieve the data from the dataset differently from the primary key way of accessing it.

What is Alternate Index?

The index built for the Alternate key is called the Alternate Index (AIX).

An alternate index is a KSDS containing index entries organized by the alternate key sorting order (ascending/ descending) of its associated base data records.An alternate index defines over a KSDS or ESDS cluster.

Notes -

  • One or more alternate indexes can be created on the dataset.
  • The AIX records are updated automatically when the base cluster is updated.

Advantages -

  • It can be defined on both KSDS and ESDS.
  • Reduces data redundancy.
  • Can have duplicates.
  • Allows datasets to be accessed by 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 alternate index as a key-sequenced cluster.
  • DATA Component - represents the alternate index's data component.
  • INDEX Component - represents the alternate index's index component.

Creating Alternative Index -

Creating an alternate index requires three steps, and those are -

Step Description
DEFINE ALTERNATEINDEX Defines the alternate index 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 alternate index and base cluster for faster access to the dataset for retrieving the data.
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.