Create Alternate Indexes in VSAM

Alternate Indexes The Alternate Index capabilities of VSAM allow the creation of one or more secondary index structures for a VSAM object ba...

Alternate Indexes

The Alternate Index capabilities of VSAM allow the creation of one or more secondary index structures for a VSAM object based upon data in the records of the object other than the primary key.  This capability allows for a Key Sequenced dataset to be accessed in a different order other than the primary key and also allows the creation of an index for a non-indexed object, such as an Entry Sequenced cluster.
There are three steps to creating an alternate index:

1. Use the DEFINE command to create the alternate index
2. Use the BLDINDEX command to build the keys for the alternate index
3. Use the DEFINE command to create a PATH relating the alternate index to the base cluster

The Alternate Index cluster is a KSDS that is used to hold the alternate key values along with their associated pointers to the records in the base cluster.
For ESDS datasets, AIX is allowed to be used only in CICS and not in BATCH.

DEFINE AIX Command

Many of the entries are identical to the DEFINE command for a CLUSTER. 
RELATE - RELATE names the existing base cluster for which the alternate index is to be defined.  The cluster named may be either a KSDS or ESDS cluster.  The cluster may not be defined with the REUSE attribute, may not be a user catalog, and may not be another alternate index.


UNIQUEKEY/NONUNIQUEKEY - UNIQUEKEY specifies that there is only one record in the base cluster corresponding to each alternate key in the alternate index.  NONUNIQUEKEY (the default) specifies that each record in the base cluster may have multiple alternate keys in the alternate index.


UPGRADE/NOUPGRADE - UPGRADE specifies that the alternate index will automatically be kept up to date when records are modified in the base cluster.  NOUPGRADE specifies that the alternate index will not be kept up to date.  There is a performance penalty when UPGRADE is specified, but there may be little utility in having an alternate index that is not kept "in sync" with the base cluster. The UPGRADE set is a group of AIX associated with the base cluster.


The RECORDSIZE refers not to the size of the base cluster record, but is computed based upon the size of the keys in both the base cluster and the alternate index and whether the keys are unique not nonunique. 
If the keys are unique, the records in the alternate index are fixed length, and the length is the value computed as

• 5 bytes of control information
• the length of the alternate key
• the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes

If the keys are nonunique, the records in the alternate index will be variable in length, and the average length is computed as:

• 5 bytes of control information
• the length of the alternate key
• the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes multiplied by the expected average number of nonunique keys

The maximum length is computed as:

• 5 bytes of control information
• the length of the alternate key
• the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes multiplied by the maximum number of nonunique keys

The contents of the 5 bytes of control information stored in the alternate index record indicate the type of the base cluster the alternate index points to and the length of the keys:

• the first byte indicates the type of base cluster - x'01' indicates KSDS; x'00' indicates ESDS
• the second byte indicates the length of the base cluster pointers in the alternate index record - x'primary key length' if the base cluster is KSDS or x'04' if the base cluster is ESDS
• the third and fourth bytes are a halfword value indicating the number of occurrences of the base cluster pointers within the alternate index record; e.g. will contain x'0001' for a unique alternate key
• the fifth byte indicates the length of the alternate key

BLDINDEX Command

BLDINDEX {  INFILE(ddname)     | INDATASET(entryname)
OUTFILE(ddname,..) | OUTDATASET(entryname,..)
[WORKFILES(ddname ddname)]
[CATALOG(catname[/password])] 

Either INFILE or INDATASET may be used to specify the base cluster.  INFILE identifies a DD statement that names the base cluster, while INDATASET names the base cluster to be located through the catalog.
Similarly, OUTFILE or OUTDATASET specify the target alternate index.  Multiple alternate indexed may be loaded from the same base cluster with a single execution of the BLDINDEX command, so OUTFILE may specify multiple DD names and OUTDATASET may specify multiple alternate index entry names.
By default, an internal sort will be performed to place the alternate keys into ascending sequence.  If there is insufficient virtual storage to perform the sort, an external sort will be performed automatically.  If an external sort is required, two work files must be provided.  The default DD names for these files is IDCUT1 and IDCUT2.  These DD names may be overridden by the WORKFILES parameter. 

DEFINE PATH Command

The PATH establishes a bridge between the base clauster and an AIX. It does not occupy any space, it is just a catalog entry which establishes the link between base cluster and AIX.

DEFINE PATH
      (NAME(entryname)     [FILE(ddname)] 

      PATHENTRY(entryname[/password])  

      UPDATE / NOUPDATE  

[TO(date)  |  FOR(days)]
      [CATALOG(catname[/password])] 

NAME/INFILE specifies the name given to the path. 

PATHENTRY specifies the name of the alternate index cluster to which this path will point.  When the path is created, accessing the VSAM object for the path will return the records from the base cluster in sequence by the values of the keys in the alternate index.

UPDATE specifies that when the records in the base cluster are modified or deleted, or when the records are added to the base cluster, each AIX in the base cluster’s upgrade set is modified to reflect the change in the cluster’s data.

In Batch, if you want to access the base cluster with alternate key, you should allocate the PATH in the JCL. The name of the DDNAME for the PATH in JCL is DDNAME of the base cluster suffixed with 1 for 1^st alrenate key and n for nth alternate key. In CICS, if you want to access base cluster with alternate key, then you should register the PATH as FCT entry.

DEFINE PATH command creates an alias for the VSAM base cluster

DEFINE ALIAS command creates an alias for non-VSAM datasets

When you open a PATH, respective base cluster will automatically be opened. UPDATE option opens the UPGARDE SET of the base cluster and it is the default. NOUPDATE opens only the base cluster and not its UPGRADE SET.