VSAM Alternate index is the additional index that is created for KSDS/ESDS datasets (but not RRDS or extended ESDS) in addition to their primary index. A VSAM alternate index allows logical records of a KSDS or of an ESDS base cluster to be accessed sequentially and randomly by more than one key field. The key of the alternate index can be a non-unique key, it can have duplicates. Alternate keys are just like the primary key in a KSDS. VSAM allows KSDS and ESDS data sets to have alternate keys. The Alternate Index enables one or more secondary index structures (other than the primary key) to be created for a VSAM object based on data in the records.
Following are the three steps to create an Alternate index.
DEFINE ALTERNATEINDEX - (NAME(alternate-index-name) - RELATE(vsam-file-name) - [CONTROLINTERVALSIZE ( size ) ] - [FREESPACE ( CI-percent [CA-percent] | 0 0 ) ] - [KEYS ( length offset | 64 0 ) ] - [UNIQUEKEY | NONUNIQUEKEY ] - [UPGRADE | NOUPGRADE] - [RECORDSIZE ( average maximum ) ]) - [DATA - (NAME(vsam-file-name.data))] - [INDEX - (NAME(vsam-file-name.index))]
| Parameter | Description |
|---|---|
| RELATE | Relates AIX with base cluster |
| KEYS | Defines the length and offset of the alternate key in base cluster |
| UNIQUEKEY | NONUNIQUEKEY | When specified UNIQUEKEY, alternate index field must hold unique values only When specified NOUNIQUEKEY, alternate index field allowed to contain duplicates |
| UPGRADE | NOUPGRADE | When specified UPGRADE, the alternate index should be modified if the base cluster is modified When specified NOUPGRADE, the alternate indexes should be left alone if the base cluster is modified. UPGRAE is default |
| RECORDSIZE | Specifies the record size of the alternate index record. It is calculated using the formula in the next table |
Alternate Index record size calculation
| UNIQUE alternate key | NOUNIQUE alternate key with maximum n duplicates | |
|---|---|---|
| KSDS | 5 + Alternate key length + Primary key length | 5 + Alternate key length + (n * Primary key length) |
| ESDS | 5 + Alternate key length + RBA size | 5 + Alternate key length + (n* RBA size) |
VSAM Alternate Index Example
//VSAMAIX JOB OZA,OZA,MSGLEVEL=(1,1), // CLASS=A,MSGCLASS=A,NOTIFY=&SYSUID //*************************************************** //*SAMPLE JCL TO CREATE ALTERNATE INDEX //*************************************************** //STEPOZA EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSOUT DD SYSOUT=* //SYSIN DD * DEFINE AIX - (NAME(XXXXXXX.YYYYYYYY.CLUSTER.ALX) - RELATE(XXXXXXX.YYYYYYYY.CLUSTER) - CISZ(4096) - KEYS(20,4) - NONUNIQUEKEY - UPGRADE- RECORDSIZE(29,29)- CYLINDERS(3,2)- FREESPACE(10,20)- ) /*
It is used to relate the Alternate index and base cluster for faster access. During definition Name of the path and related alternate index are required.
DEFINE PATH - NAME(alternate-index-path-name) - PATHENTRY(alternate-index-name)) UPDATE/NOUPDATE/UPGRADE/NOUPGRADE
NAME – It is used to specify the Alternate Index Path Name
PATHENTRY – It is used to specify an Alternate Index Name.
UPDATE/NOUPDATE – Specifies whether the upgrade set should be updated when this path is processed. If you sepecify UPDATE, VSAM will open all the alternate indexes in the base cluster’s upgrade set when you open the path. And it will maintain those upgrade set members when you process the data set.
UPGRADE/NOUPGRADE – Specifies whether the alternate index is part of the base cluster’s upgrade set. An upgrade set consists of all of a base cluster’s upgradeable alternate indexes.
If you want VSAM to update the alternate index whenever you make a change to the base cluster, code the UPGRADE option in the DEFINE AIX command, or allow it to default to UPGRADE.
On the DEFINE AIX command, the UPGRADE/NOUPGRADE parameter specifies whether VSAM should include that particular alternate index in the base cluster’s upgrade set. VSAM always maintains this upgrade set when you process the base cluster via its primary key. When you use the UPDATE/NOUPDATE parameter of the DEFINE PATH command, you have additional control over whether VSAM maintains the upgrade set when you process the base cluster via an alternate key.
//VSAMPATH JOB OZA,OZA,MSGLEVEL=(1,1), // CLASS=A,MSGCLASS=A,NOTIFY=&SYSUID //*************************************************** //*SAMPLE JCL TO DEFINE PATH //*************************************************** //STEPOZA EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * DEFINE PATH (NAME(XXXXXXX.YYYYYYYY.CLUSTER.PATH) - PATHENTRY(XXXXXXX.YYYYYYYY.CLUSTER.ALX) UPDATE /*
It is used to build the alternate index. It reads all the records in the VSAM indexed data set (or base cluster) and extracts the data needed to build the alternate index.
BLDINDEX - INDATASET(vsam-cluster-name) - OUTDATASET(alternate-index-name))
INDATASET – It is used to specify the VSAM Cluster Name
OUTDATASET – It is used to specify an Alternate Index Name.
//VSAMINDX JOB OZA,OZA,MSGLEVEL=(1,1), // CLASS=A,MSGCLASS=A,NOTIFY=&SYSUID //*************************************************** //*SAMPLE JCL TO BUILD ALTERNATE INDEX //*************************************************** //STEP EXEC PGM=IDCAMS //SYSPRINT DD SYSOUT=* //SYSIN DD * BLDINDEX - INDATASET(IXXXXXXX.YYYYYYYY.CLUSTER) - OUTDATASET(IXXXXXXX.YYYYYYYY.CLUSTER.ALX) /*
A well-maintained product backlog is crucial for successful product development. It serves as a single…
Incremental value to the customer refers to the gradual delivery of small, functional parts of…
A Product Market refers to the group of potential customers who might be interested in…
The Professional Agile Leadership - Evidence-Based Management (PAL-EBM) certification offered by Scrum.org is designed for…
The Professional Agile Leadership (PAL I) certification, offered by Scrum.org, is designed to equip leaders…
Choosing the right Scrum Master Certification depends on your current experience and career goals. If…