VSAM - Alternate Index

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. The key of alternate index can be a non-unique key, it can have duplicates.

Creation of Alternate Index:

There are three steps to creating an alternate index.

1. Using DEFINE command to create the alternate index.

2. Using DEFINE command to create a PATH relating the alternate index to the base cluster.

3. Using BLDINDEX command to build the keys for the alternate index.

Let us see each steps in detail.

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 VSAM - Define Cluster Syntax chapter and other new parameters are explained below.

Syntax Explanation:

• DEFINE AIX

  Define AIX command is used to define Alternate Index and specify parameter attributes for its components.

• NAME

  NAME specifies the name of Alternate Index.

• RELATE

  RELATE specifies the name of the VSAM cluster for which the alternate index is created.

• NONUNIQUEKEY / UNIQUEKEY

  UNIQUEKEY specifies that the alternate index is unique and NONUNIQUEKEY specifies that duplicates may exist.

• 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 for VSAM.

//JOBNAME1 JOB 'IBMMAINFRAMER',JOB CLASS=C,PRTY=10,MSGCLASS=C,
//            MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//STEP001  EXEC PGM=IDCAMS
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  *
   DEFINE AIX                           -
      (NAME(racfid.VSAM.KSDSAIX.TEST)   -
      RELATE(racfid.VSAM.KSDSFILE.TEST) -
      CISZ(4096)                        -
      FREESPACE(20,20)                  -
      KEYS(20,7)                        -
      NONUNIQUEKEY                      -
      UPGRADE                           -
      RECORDSIZE(80,80))                -
  DATA                                  -
     (NAME(racfid.VSAM.KSDSAIX.TEST.DATA))       -
  INDEX                                 -
    (NAME(racfid.VSAM.KSDSAIX.TEST.INDEX))
/*

If you execute the above JCL. Job should complete successfully with MAXCC = 0 and it will create racfid.VSAM.KSDSAIX.TEST 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))

Syntax Explanation:

Example:

//JOBNAME2 JOB 'IBMMAINFRAMER',JOB CLASS=C,PRTY=10,MSGCLASS=C,
//            MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//STEP001  EXEC PGM=IDCAMS
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  *
DEFINE PATH                               -
   NAME(racfid.VSAM.KSDSAIX.TEST.PATH)    -
   PATHENTRY(racfid.VSAM.KSDSAIX.TEST))
/*

If you execute the above JCL. Job should complete successfully 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))

Syntax Explanation:

Example:

//JOBNAME3 JOB 'IBMMAINFRAMER',JOB CLASS=C,PRTY=10,MSGCLASS=C,
//            MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*
//STEP001  EXEC PGM=IDCAMS
//SYSPRINT DD  SYSOUT=*
//SYSIN    DD  *
   BLDINDEX                              -
    INDATASET(racfid.VSAM.KSDSFILE.TEST) -
    OUTDATASET(racfid.VSAM.KSDSAIX.TEST))
/*

If you execute the above JCL. Job should complete successfully with MAXCC = 0 and it will build the index.

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!