VSAM - Alternate Index

Alternate index are the additional index that are created for KSDS/ESDS datasets in addition to their primary index. An alternate index provides access to records by using more than one key. The key of alternate index can be a non-unique key, it can have duplicates.

Creation of Alternate Index

Following steps are used to create an Alternate Index −

• Define Alternate Index
• Define Path
• Building Index

Define Alternate Index

Alternate Index is defined using DEFINE AIX command.

DEFINE AIX                              -
(NAME(alternate-index-name)             -
RELATE(vsam-file-name)                  -
CISZ(number)                            -
FREESPACE(CI-Percentage,CA-Percentage)  -
KEYS(length offset)                     -
NONUNIQUEKEY / UNIQUEKEY                -
UPGRADE / NOUPGRADE                     -
RECORDSIZE(average maximum))            -
DATA                                    -
   (NAME(vsam-file-name.data))          -
INDEX                                   -
   (NAME(vsam-file-name.index))

Above syntax shows the parameters which are used while defining Alternate Index. We have already discussed some parameters in Define Cluster Module and some of the new parameters are used in defining Alternate Index which we will discuss here −

Sr.No	Parameters with Description
1	DEFINE AIX Define AIX command is used to define Alternate Index and specify parameter attributes for its components.
2	NAME NAME specifies the name of Alternate Index.
3	RELATE RELATE specifies the name of the VSAM cluster for which the alternate index is created.
4	NONUNIQUEKEY / UNIQUEKEY UNIQUEKEY specifies that the alternate index is unique and NONUNIQUEKEY specifies that duplicates may exist.
5	UPGRADE / NOUPGRADE UPGRADE specifies that the alternate index should be modified if the base cluster is modified and NOUPGRADE specifies that the alternate indexes should be left alone if the base cluster is modified.

Example

Following is a basic example to show how to define an Alternate Index in JCL −

//SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
//STEP1  EXEC PGM = IDCAMS
//SYSPRINT DD  SYSOUT = *
//SYSIN    DD  *
   DEFINE AIX (NAME(MY.VSAM.KSDSAIX)      -
   RELATE(MY.VSAM.KSDSFILE)               -
   CISZ(4096)                             -
   FREESPACE(20,20)                       -
   KEYS(20,7)                             -
   NONUNIQUEKEY                           -
   UPGRADE                                -
   RECORDSIZE(80,80))                     -
   DATA(NAME(MY.VSAM.KSDSAIX.DATA))       -
   INDEX(NAME(MY.VSAM.KSDSAIX.INDEX))
/*

If you will execute the above JCL on Mainframes server. It should execute with MAXCC = 0 and it will create MY.VSAM.KSDSAIX Alternate Index.

Define Path

Define Path is used to relate the alternate index to the base cluster. While defining path we specify the name of the path and the alternate index to which this path is related.

DEFINE PATH                        -
NAME(alternate-index-path-name)    -
PATHENTRY(alternate-index-name))

Above syntax has two parameters. NAME is used to specify the Alternate Index Path Name and PATHENTRY is used to specify Alternate Index Name.

Example

Following is a basic example to define Path in JCL −

//SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
//STEP1  EXEC PGM = IDCAMS
//SYSPRINT DD  SYSOUT = *
//SYSIN    DD  *
DEFINE PATH                          -
   NAME(MY.VSAM.KSDSAIX.PATH)    -
   PATHENTRY(MY.VSAM.KSDSAIX))
/*

If you will execute the above JCL on Mainframes server. It should execute with MAXCC = 0 and it will create path between Alternate Index to the base cluster.

Building Index

BLDINDEX command is used to build the alternate index. BLDINDEX 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))

Above syntax has two parameters. INDATASET is used to specify the VSAM Cluster Name and OUTDATASET is used to specify Alternate Index Name.

Example

Following is a basic example to Build Index in JCL −

//SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
//STEP1  EXEC PGM = IDCAMS
//SYSPRINT DD  SYSOUT = *
//SYSIN    DD  *
   BLDINDEX                           -
   INDATASET(MY.VSAM.KSDSFILE)        -
   OUTDATASET(MY.VSAM.KSDSAIX))
/*

If you will execute the above JCL on Mainframes server. It should execute with MAXCC = 0 and it will build the index.