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 Alternate index
- Define path
- Build index
Define VSAM 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) |
- In the above-shown calculations, the first five bytes are used for storing control information of the Alternate Index record, and below we have described the purpose of each byte:-
- Byte 1: Type of cluster; Hex ’00’ indicates ESDS and Hex ‘01’ indicates KSDS
- Byte 2: Used to hold the length of base cluster pointer in the alternate index; Primary key length for KSDS and Hex ‘04’ for ESDS
- Byte 3 & 4: Half word value indicating the number of occurrences of the base cluster pointers within the alternate index record; X’0001′ for a unique alternate key
- Byte 5: Length of alternate key
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)- ) /*
Define PATH
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.
VSAM Alternate Index Example
//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 /*
Build INDEX
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.
VSAM Alternate Index Example
//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) /*
VSAM Tutorials
- VSAM DEFINE CLUSTER using IDCAMS with Examples
- VSAM Alternate Index using IDCAMS with Examples
- VSAM REPRO using IDCAMS with Examples
- VSAM PRINT using IDCAMS with Examples
- VSAM VERIFY EXAMINE using IDCAMS with Examples
- VSAM DELETE using IDCAMS with Examples
- VSAM EXPORT-IMPORT using IDCAMS with Examples