5 Must Know Tips If You Want To Use VSAM Alternate Index

Key relation between Alternate Index, Path and Build Index
[Key relation between Alternate Index, Path and Build Index]
Alternate indexes (AIXs) allow logical records of a KSDS or of an ESDS (in this context, a base cluster) to be accessed sequentially and directly by more than one key field.

  1. AIX eliminate the need to store the same data in different sequences in multiple clusters for Index Set Forward horizontal pointer at same level Vertical pointers to next lower level index records Just one CI in the top Control Interval Control Interval Control Interval Control Interval Sequence Set Index Set Control Interval Control Interval Control Interval 16 VSAM Demystified various applications.
  2. Each alternate index is a KSDS cluster that consists of an index component and a data component. Any field in the base cluster record can be used as an alternate key. It can also overlap the primary key (in a KSDS), or with any other alternate key.
  3. The same base cluster can have several AIXs varying the alternate key. That is, the alternate key can have repeated values. Also, there can be more than one primary key value for the same alternate key value. As an example, the primary key is an employee number and the alternate key is the department name. Obviously, the same department name can have several employee numbers.
  4. The records in the data component of an AIX cluster contain the alternate key value and all the primary keys corresponding to the alternate key value (pointers to data component in the base cluster). The primary keys in the logical record are in ascending sequence within an alternate index value.
  5. If you have many primary keys per alternate key, consider defining the AIX as spanned and compressed. Remember that a logical record cannot cross control areas.

Typical Process flow of Alternate index in VSAM

  • By using the IDCAMS utility program, you can define and then create AIX when the BLDINDEX command is specified.
  • An AIX is defined only after its associated base cluster has been defined. It can be built only after its base has been loaded with at least one record. The BLDINDEX command causes a sequential scan of the specified base cluster.
  • During this scan, alternate key values and primary keys (for a KSDS) or record RBAs (for an ESDS) are extracted and put together to form alternate index records.
  • These records are sorted by ascending alternate keys. The alternate index records are then constructed and written.

Role of Path to use Alternate Index.

Before you access a KSDS or ESDS through an alternate index, a path must be defined in the catalog. A path is the means by which a base cluster is accessed through its alternate indexes. A path is defined and named by using the IDCAMS DEFINE PATH command. At least one path must be defined for each of the alternate indexes through which they access the base cluster. The path name refers to the base cluster and alternate index pair. When a
program opens a path for processing, both the base cluster and the alternate index are opened. A base cluster plus all its AIX is called a sphere.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.