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 −

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.