VSAM Alternate Index (AIX)

A VSAM base cluster has one primary key (for a KSDS) or no key (for an ESDS). Sometimes you need to access the same data by a different field—for example, find all employees in a department by department ID when the primary key is employee ID. An alternate index (AIX) is a secondary index that lets you do that. It is a separate VSAM structure that stores alternate key values and pointers to the base cluster records. You define the AIX, define a path that connects it to the base cluster, and build the index from the base data. This page covers the purpose of an alternate index, alternate keys and duplicate keys (UNIQUEKEY vs NONUNIQUEKEY), and the three steps to create and use one: DEFINE ALTERNATEINDEX, DEFINE PATH, and BLDINDEX.

Purpose of an Alternate Index

The primary key of a KSDS determines the order of records and how you do key-based reads. If your application also needs to find records by another field (e.g. department code, last name, or account type), you have two options: scan the file sequentially (slow) or build a secondary index. An alternate index is that secondary index. It is a separate cluster with its own data and index components. The AIX data component holds alternate key values and pointers (RBAs or RRNs) to the base cluster. When you open the base cluster through the path (not by the base cluster name), the system uses the alternate index to resolve an alternate key to the correct base record. So the purpose of an AIX is to allow access by a key other than the primary key, without changing the base cluster structure.

Alternate indexes can be defined only over a KSDS or ESDS base cluster. You cannot define an AIX over an RRDS, LDS, reusable cluster, catalog, another AIX, or non-VSAM dataset. For ESDS, the base has no primary key; the AIX gives you keyed access by an alternate field. For KSDS, the AIX gives you a second (or additional) way to access the same records by a different key.

Alternate Keys and Duplicate Keys

The alternate key is the field in the base record that you use to build the AIX. You specify its length and offset in the base record when you define the AIX (KEYS(length offset)). That field can be unique (e.g. a second identifier that is unique per record) or non-unique (e.g. department ID, where many records share the same value). You tell VSAM which case applies by specifying UNIQUEKEY or NONUNIQUEKEY on DEFINE ALTERNATEINDEX.

With UNIQUEKEY, every value in the alternate key field must be unique across all base records. The AIX has one entry per base record (one-to-one). With NONUNIQUEKEY, the same alternate key value can appear in many base records. The AIX stores one index entry per distinct key value, and that entry can point to multiple base records. When you read via the path with a given alternate key, you get the first matching base record; you can then use READ NEXT (or equivalent) to get the rest. So duplicate keys mean "multiple base records can have the same alternate key"; the AIX and the access method handle returning each of them.

Three Steps to Create an Alternate Index

Creating and using an AIX involves three separate IDCAMS steps. You must do them in order: first define the AIX, then define the path, then build the index. If you skip the path, applications cannot use the AIX to access the base cluster. If you skip BLDINDEX, the AIX is empty and no alternate key reads will find any data.

Step 1: DEFINE ALTERNATEINDEX (DEFINE AIX)

DEFINE ALTERNATEINDEX (or DEFINE AIX) creates the alternate index cluster and its DATA and INDEX components. It does not build the index keys; it only allocates the structure. You must specify RELATE(base-cluster-name) so the system knows which base cluster this AIX is for. Other important parameters include KEYS(length offset) for the alternate key position in the base record, UNIQUEKEY or NONUNIQUEKEY, RECORDSIZE for the AIX records (which hold key and pointer information), FREESPACE, CISZ, and space allocation (e.g. CYLINDERS). UPGRADE means the AIX will be automatically updated when the base cluster is updated; NOUPGRADE means it will not (and you would run BLDINDEX again to refresh it).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
//DEFAIX   EXEC PGM=IDCAMS
//SYSPRINT DD   SYSOUT=*
//SYSIN    DD   *
  DEFINE ALTERNATEINDEX ( -
    NAME(USERID.EMPL.DEPTAIX) -
    RELATE(USERID.EMPL.KSDS) -
    KEYS(5 24) -
    NONUNIQUEKEY -
    UPGRADE -
    RECORDSIZE(47 47) -
    FREESPACE(10 20) -
    CISZ(4096) -
    CYLINDERS(2 1) -
    VOLUMES(SYSVOL)) -
  DATA (NAME(USERID.EMPL.DEPTAIX.DATA)) -
  INDEX (NAME(USERID.EMPL.DEPTAIX.INDEX))
/*
 

Here the base cluster is USERID.EMPL.KSDS. The alternate key is 5 bytes at offset 24 in the base record (e.g. department ID). NONUNIQUEKEY allows many records to share the same department. UPGRADE keeps the AIX in sync when the base is updated. The AIX has its own DATA and INDEX components with explicit names.

Step 2: DEFINE PATH

DEFINE PATH creates the logical link between the AIX and the base cluster. Without a path, the AIX exists but there is no way for an application to "open the base cluster by alternate key." The path has a name (the path name) and a PATHENTRY (the AIX name). Applications open the path name in JCL (DSN=path-name) and in the program. When they read by the alternate key, the system uses the AIX to find the pointer(s) to the base record(s) and returns the base cluster record(s). So the path is what you use in DSN= when you want alternate-key access; the base cluster name is what you use when you want primary-key or sequential access.

1
2
3
4
  DEFINE PATH -
    NAME(USERID.EMPL.DEPTAIX.PATH) -
    PATHENTRY(USERID.EMPL.DEPTAIX)
 

NAME is the path name (what you put in DSN= for alternate-key access). PATHENTRY is the AIX cluster name you defined in step 1. After this, USERID.EMPL.DEPTAIX.PATH can be used to open the base cluster by department ID.

Step 3: BLDINDEX

BLDINDEX builds the alternate index from the base cluster. It reads every record in the base cluster (INDATASET), extracts the alternate key (and builds pointer information), and writes the AIX (OUTDATASET). You run BLDINDEX after the base cluster has been loaded (or whenever you want to rebuild the AIX, e.g. after a NOUPGRADE AIX has gotten out of date). The job must have access to both the base cluster and the AIX.

1
2
3
4
  BLDINDEX -
    INDATASET(USERID.EMPL.KSDS) -
    OUTDATASET(USERID.EMPL.DEPTAIX)
 

INDATASET is the base cluster name; OUTDATASET is the AIX cluster name. After BLDINDEX completes, the AIX contains an entry for each alternate key value (and for NONUNIQUEKEY, multiple pointers where keys duplicate). Applications can then open the path and read by alternate key.

Using the Alternate Index in Applications

To use the AIX, you allocate the path name in JCL (DSN=path-name) and open it in your program as you would a KSDS. The record you read is the base cluster record; the key you use for START or random read is the alternate key. The access method uses the AIX to find the base record(s) that match that key and returns them. For NONUNIQUEKEY, after a successful read you can issue READ NEXT to get the next record with the same alternate key. So from the program's point of view, the file behaves like a keyed file where the key is the alternate key, and the record is the full base record.

When to Use UPGRADE vs NOUPGRADE

UPGRADE is the normal choice when the base cluster is updated (inserts, deletes, rewrites). The system updates the AIX automatically so that alternate-key access always sees current data. NOUPGRADE means the AIX is not updated when the base changes; the AIX can become stale. Use NOUPGRADE only when the base is read-only or when you plan to run BLDINDEX periodically to refresh the AIX. For most production files that are updated, specify UPGRADE.

Key Takeaways

• An alternate index (AIX) lets you access a KSDS or ESDS base cluster by a key other than the primary key.
• UNIQUEKEY means the alternate key is unique per record; NONUNIQUEKEY allows duplicate alternate key values (e.g. many records per department).
• Create an AIX in three steps: DEFINE ALTERNATEINDEX (creates AIX cluster), DEFINE PATH (links AIX to base), BLDINDEX (builds index from base data).
• Applications open the path name (not the base cluster name) to access by alternate key; the path name is what you put in DSN=.
• UPGRADE keeps the AIX in sync with base updates; NOUPGRADE requires periodic BLDINDEX to refresh the AIX.

Explain Like I'm Five

Imagine a big book (the base cluster) where everything is in order by page number. The alternate index is like a second table at the back that says "if you want to find things by color, look here." So you have one book but two ways to find stuff: by page number (primary key) or by color (alternate key). The path is the rule that says "when someone asks by color, use the color list to find the right page." You have to build that color list once (BLDINDEX), and if the book changes, you can either update the list automatically (UPGRADE) or rebuild it later (NOUPGRADE).

Test Your Knowledge