Alternate Index (for Experienced)
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 -
01 EMPL-REC. 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). 05 EMPL-DEPT-NAME PIC X(10).
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.
- The primary key doesn't allow duplicates, whereas the alternate key can have duplicates.
- Alternate keys are just like the primary key in a KSDS.
- The alternate key should be defined when the dataset is created.
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. It provides another way of finding records from the data component.
An alternate index defines over a KSDS or ESDS cluster. An alternate index not allowed to define on a reusable cluster, a fixed or variable-length RRDS, an extended ESDS, a catalog, another alternate index, an LDS, or a non-VSAM dataset.
- A distinct index is defined for each alternative key.
- One or more alternate indexes can be created on the dataset.
- An alternate index also 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.
- Alternate indexes do not support a reusable base cluster. So NOREUSE should be specified.
- It can be defined on both KSDS and ESDS.
- Reduces data redundancy.
- Can have duplicates.
- Easy to define using IDCAMS.
- Allows datasets to be accessed by both sequentially and randomly.
- Reduce the performance.
- Complex to update logic.
- Increase alternate indexes built on a KSDS may reduce performance as alternate key access requires twice as many I/Os.
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.
Alternate Index Building Sequence -
The sequence for building an alternate index is as follows -
- Define the base cluster using either the ALLOCATE, the DEFINE CLUSTER, or JCL.
- Load the base cluster either by using the REPRO command or writing a program to load the dataset.
- Define the alternate index using the DEFINE ALTERNATEINDEX command.
- Relate the alternate index to the base cluster using the DEFINE PATH command. Entries represent the base cluster and alternate index in the same catalog.
- Build the alternate index using the BLDINDEX command.
Building Alternative Index -
Building an alternate index requires three steps, and those are -
|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.|