ALTERNATE index creation(DEFINE ALTERNATEINDEX,BLDINDEX,DEFINE PATH).

Alternate indexes:

·        Alternate indexes 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.

·        Alternate indexes have following advantages:

1)     Allows for a Key Sequenced dataset to be accessed in a different order other than the primary key.

2)     Allows the creation of an index for a non-indexed object, such as an Entry Sequenced cluster.

·        There are three steps to creating an alternate index:

1)     Use the DEFINE command to create the alternate index

2)     Use the BLDINDEX command to build the keys for the alternate index

3)     Use the DEFINE command to create a PATH relating the alternate index to the base cluster

·        The Alternate Index cluster is a Key Sequenced cluster itself that is used to hold the alternate key values along with their associated pointers to the records in the base cluster.

·        Syntax:

    DEFINE ALTERNATEINDEX
    (NAME(entryname)
    RELATE(entryname[/password])
    {CYLINDERS(primary[ secondary])  |  RECORDS(primary[ secondary])  |
      TRACKS(primary[ secondary])}
    VOLUMES(volser[ volser...])
    [BUFFERSPACE(size)]
    [CONTROLINTERVALSIZE(size)]
    [ERASE  |  NOERASE]
    [FILE(ddname)]
    [FREESPACE(CI-percent[ CA-percent]|0 0)
    [IMBED  |  NOIMBED]
    [KEYS(length offset|64 0)]
    [MODEL(entryname[/password][ catname[/password
    [RECORDSIZE(average maximum|4086 32600)]
    [REPLICATE  |  NOREPLICATE]
    [REUSE  |  NOREUSE]
    [SPEED  |  RECOVERY]
    [TO(date)  |  FOR(days)]
    [UNIQUE  |  SUBALLOCATION]
    [UNIQUEKEY  |  NONUNIQUEKEY]
    [UPGRADE  |  NOUPGRADE])

  [DATA
    ([NAME(entryname)])

  [INDEX
    ([NAME(entryname)])

  [CATALOG(catname[/password])] 

§  RELATE: Names the existing base cluster for which the alternate index is to be defined. The cluster named may be either a KSDS or ESDS cluster.

§  UNIQUEKEY specifies that there is only one record in the base cluster corresponding to each alternate key in the alternate index. 

§  NONUNIQUEKEY (the default) specifies that each record in the base cluster may have multiple alternate keys in the alternate index.

§  UPGRADE specifies that the alternate index will automatically be kept up to date when records are modified in the base cluster. 

§  NOUPGRADE specifies that the alternate index will not be kept up to date.  There is a performance penalty when UPGRADE is specified.

§  The RECORDSIZE refers not to the size of the base cluster record, but is computed based upon the size of the keys in both the base cluster and the alternate index and whether the keys are unique not no unique.

·        If the keys are unique, the records in the alternate index are fixed length, and the length is the value computed as

·         5 bytes of control information

·         the length of the alternate key

·         the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes

·        If the keys are nonunique, the records in the alternate index will be variable in length, and the average length is computed as:

·         5 bytes of control information

·         the length of the alternate key

·         the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes multiplied by the expected average number of nonunique keys

·        The maximum length is computed as:

·         5 bytes of control information

·         the length of the alternate key

·         the length of the primary key (for KSDS) or the length of an RBA (for ESDS), which is 4 bytes multiplied by the maximum number of nonunique keys.

·        Alternate index is a KSDS itself and hence has its own DATA and INDEX elements.

·        BY running the DEFINE ALTERNATE INDEX all that has been created is a cluster to contain the alternate keys.  In the next step, the keys will be built from the base cluster with the BLDINDEX command.

·        Following is the syntax for BLDINDEX command:

BLDINDEX {INFILE(ddname[/password])  |   INDATASET(entryname[/password])}
    {OUTFILE(ddname[/password] [ ddname[/password]...])  |
          OUTDATASET(entryname[/password] [ entryname[/password]...])}
    [WORKFILES(ddname ddname)]

    [CATALOG(catname[/password])] 

·        AMS reads the base cluster sequentially and constructs alternate key/pointer pairs from each data record in the base cluster.  If the base cluster is a KSDS, each pointer consists of a prime key value; if the base cluster is an ESDS, each pointer consists of an RBA pointer.  

AMS sorts the alternate key/pointer pairs to load records into ascending key sequence.

 INFILE or INDATASET may be used to specify the base cluster. INFILE specifies the DD Name and INDATASET specifies the name of the Base cluster.

OUTFILE or OUTDATASET specify the target alternate index.  Multiple alternate indexed may be loaded from the same base cluster with a single execution of the BLDINDEX command, so OUTFILE may specify multiple DD names and OUTDATASET may specify multiple alternate index entry names.

Thus multiple AIX can be build at the same time.

·        By default, an internal sort will be performed to place the alternate keys into ascending sequence.  If there is insufficient virtual storage to perform the sort, an external sort will be performed automatically.  If an external sort is required, two work files must be provided.  The default DD names for these files is IDCUT1 and IDCUT2.  These DD names may be overridden by the WORKFILES parameter. 

·        Simplest Example would be as follows:

  BLDINDEX INDATASET(SM017R.STUDENT.FILE)                   - 

           OUTDATASET(SM017R.STUDENT.AIX)                     

SM017R.STUDENT.FILE is the actual file name and SM017R.STUDENT.AIX is the alternate index being built. This must be first defined using DEFINE statement.

·        Syntax for the DEFINE PATH command:

DEFINE PATH     (NAME(entryname)     PATHENTRY(entryname[/password])     [FILE(ddname)]     [MODEL(entryname[/password] [ catname[/password]])]     [TO(date)  |  FOR(days)]     [CATALOG(catname[/password])]

Note that I have simplified the syntax diagram by excluding advanced optional parameters.

NAME specifies the name given to the path.  PATHENTRY specifies the name of the alternate index cluster to which this path will point.  When the path is created, accessing the VSAM object for the path will return the records from the base cluster in sequence by the values of the keys in the alternate index.

Example:

DEFINE PATH (                                             - 

               NAME(SM017R.STUDENT.NAME.IX)                 - 

               PATHENTRY(SM017R.STUDENT.AIX) )